如何根据事件类型数据填写行 [英] How to fill in rows based on event type data

问题描述

所以我的表有2列：小时和customerID。每个客户将有两行，一行对应于他/她进入商店的时间，一行对应于他/她离开商店的时间。有了这些数据，我想创建一个表，该表每个小时都有顾客到店。例如，客户X在下午1点进入商店，然后在下午5点离开商店，因此将有5行（每小时1条），如下面的屏幕截图所示。

So my table has 2 columns: hour and customerID. Every customer will have 2 rows, one corresponding to hour that he/she came into the store, and one corresponding to hour that he/she left the store. With this data, I want to create a table that has every hour that a customer has been in the store. For example, a customer X entered the store at 1PM and left at 5PM, so there would be 5 rows (1 for each hour) like the screenshot below.

这是我的现在尝试进行以下操作：

Here's my attempt that's now:

select
    hour
    ,first_value(customer_id) over (partition by customer_id order by hour rows between unbounded preceding and current row) as customer_id
FROM table 

推荐答案

假定：

• 您正在运行Postgres

• you are running Postgres

一个给定的客户在表中总是正好有两行

a given customer always has exactly two rows in the table

小时具有类似日期的数据类型

hour is of a date-like datatype

然后，一个选择是使用 generate_series（）进行横向连接，例如：

Then one option is to use generate_series() with a lateral join, like so:

select t.customer_id, x.hour
from (
    select customer_id, min(hour) min_hour, max(hour) max_hour 
    from mytable 
    group by customer_id
) t
cross join lateral generate_series(min_hour, max_hour, '1 hour') x(hour)
order by t.customer_id, x.hour

$ b加入横向generate_series（min_hour，max_hour，'1 hour'）x（hour）
订单
$ b

DB Fiddlde上的演示 ：

Demo on DB Fiddlde:

customer_id | hour               
:---------- | :------------------
X           | 2019-04-01 13:00:00
X           | 2019-04-01 14:00:00
X           | 2019-04-01 15:00:00
X           | 2019-04-01 16:00:00
X           | 2019-04-01 17:00:00
Y           | 2019-04-01 17:00:00
Y           | 2019-04-01 18:00:00
Y           | 2019-04-01 19:00:00

这篇关于如何根据事件类型数据填写行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！