我正在尝试使用Snowflake获得基于时间戳的组的最后值。
我有下面的表格，
| ISSUE_ID | ISSUE_ID | FIELD_TIME | FIELD_NAME | FIELD_VALUE| STATUS
| -------- | ---------- | ---------- | ----------| ----------| ----------
|19229| X1|2021-08-01 09:04:35.234000000 +00:00|Status|10010|PENDING
|19229| X1|2021-08-01 09:04:35.234000000 +00:00|Number of Products|55|PENDING
|19229| X1|2021-08-01 09:04:35.234000000 +00:00|Number of SKU not created|34|PENDING
|19229| X1|2021-08-01 09:04:35.234000000 +00:00|Number of SKU live|21|PENDING
|19229| X1|2021-08-01 09:04:35.234000000 +00:00|Date of SKU live|2021-08-12|PENDING
|19229| X1|2021-08-08 06:19:05.209000000 +00:00|Status|10010|PENDING
|19229| X1|2021-08-08 11:43:51.953000000 +00:00|Status|10010|PENDING
|19229| X1|2021-08-12 06:12:15.185000000 +00:00|Number of Products|55|PENDING
|19229| X1|2021-08-12 06:27:32.199000000 +00:00|Number of SKU not created|31|PENDING
|19229| X1|2021-08-12 09:14:16.178000000 +00:00|Number of SKU live|24|PENDING
我希望表中每个值都有一列(例如，产品数量，SKU日期等)，并且该列只包含当天的最后一个值。如下表所示，
| ISSUE_ID | ISSUE_ID | FIELD_TIME | Number of Products | Number of SKU live| Date of SKU live|Number of SKU not created|Work_In_Progress_Date|Pending_Date|Status|Resolution|
|--------|----------|---------- |----------|----------|----------|----------|----------
|19229 | X1 |2021-08-01 |55 |21 |2021-08-01|34 | | |PENDING | null
|19229 | X1 |2021-08-08 | | | | | | |PENDING | null
|19229 | X1 |2021-08-12 |55 |24 |2021-08-01|31 |2021-08-12| 2021-08-12|PENDING | null
我尝试过last_value(FIELD_VALUE) over (partition by FIELD_NAME, ISSUE_ID order by field_time)，但它给了我23行而不是3行的重复值。我也试过lag()，但没有运气。
如果有人对如何做这个转换有一个想法，请帮助我。
这是我的SQL代码，
select
t.ISSUE_ID, t.issue_name,
-- t.created_date,
t.field_time::date as field_time,
max(case when field_name = 'Number of Products' then field_value end) as Number_of_Products,
max(case when field_name = 'Number of SKU live' then field_value end) as Number_of_SKU_Live,
max(case when field_name = 'Number of SKU not created' then field_value end) as Number_of_SKU_Not_Created,
max(case when field_name = 'Date of SKU live' then field_value end) as Date_of_SKU_Live,
max(case when field_value = '10020' then date(t.field_time) end) as Work_In_Progress_Date,
max(case when field_value = '10010' then date(t.field_time) end) as Pending_Date,
t.status, t.resolution
from
(select fh.ISSUE_ID,
i.issue_name,
date(i.created_date) as created_date,
fh.TIME as field_time,
f.name as field_name,
fh.value as field_value,
i.status,
i.resolution
from JIRA.ISSUE_FIELD_HISTORY fh
left join JIRA.FIELD f on fh.FIELD_ID = f.ID and f._FIVETRAN_DELETED = 0
left join (select i0.created as created_date,r.name as resolution, i0.id, i0.key as issue_name, i0.status as status_id, s.name as status
from JIRA.issue i0
left join JIRA.status s on i0.status = s.ID
left join JIRA.RESOLUTION r on i0.RESOLUTION = r.ID
where i0._FIVETRAN_DELETED = 0
and i0.key like 'PIM%')
i on i.id = fh.ISSUE_ID
where fh.ISSUE_ID in (select ID from ISSUE where PROJECT = 10041)
and fh.FIELD_ID in ('customfield_10067', 'customfield_10063', 'customfield_10066', 'customfield_10068', 'status', 'resolution')
-- and issue_name = 'PIM-11'
qualify row_number() over (partition by issue_id, field_time::date, field_name order by field_time desc) = 1
order by field_time) t
group by issue_id, issue_name,created_date, field_time::date, status, resolution
您可以使用qualify来获取最近的值:
select t.*
from t
qualify row_number() over (partition by issue_id, field_time::date, field_name order by field_time desc) = 0;
然后总:
select issue_id, field_time::date,
max(case when field_name = 'Number of Products' then field_value end) as number_of_products,
max(case when field_name = 'Number of SKU live' then field_value end) as number_of_sku_live,
. . .
from (select t.*
from t
qualify row_number() over (partition by issue_id, field_time::date, field_name order by field_time desc) = 0
) t
group by issue_id, field_time::date