我在 BQ 表中有兩個日期列。pageview_date和edited_date，以及id列。我需要逐行輸出資料，對于每條記錄，我想從edited_date列中獲取一個值，該值是該列中的最新日期，但不晚于pageview_date值本身。如果兩個日期相等，則保持原樣。它還必須與 id 相對應。資料如下所示：
id pageview_date edited_date
A 03/01/22 02/28/22
A 03/01/22 02/02/22
A 03/01/22 02/02/22
B 03/01/22 01/01/22
B 03/01/22 01/01/22
B 03/01/22 01/31/22
C 03/01/22 04/01/22
C 03/01/22 03/25/22
C 03/01/22 03/01/22
期望的輸出是：
id pageview_date edited_date
A 03/01/22 02/28/22
A 03/01/22 02/28/22
A 03/01/22 02/28/22
B 03/01/22 01/31/22
B 03/01/22 01/31/22
B 03/01/22 01/31/22
C 03/01/22 03/01/22
C 03/01/22 03/01/22
C 03/01/22 03/01/22
uj5u.com熱心網友回復：
一種方法是在由磁區的列中使用MAX視窗函式：edited_dateid
with sample as (
select 'a' as id, DATE('2022-03-01') as pageview_date, DATE('2022-02-28') as edited_date
UNION ALL
select 'a' as id, DATE('2022-03-01') as pageview_date, DATE('2022-03-28') as edited_date
UNION ALL
select 'a' as id, DATE('2022-03-01') as pageview_date, DATE('2022-01-28') as edited_date
)
SELECT
id,
pageview_date,
MAX(IF(edited_date <= pageview_date, edited_date, null)) OVER (PARTITION BY id) as new_edited_date
FROM sample
請注意，如果edited_date在 pageview_date 之前沒有，new_edited_date則將為null.
轉載請註明出處，本文鏈接：https://www.uj5u.com/gongcheng/487184.html
上一篇：日期時間時區抓取Python