我试着应用了几个here的解决方案,但我的问题似乎与本文中的OP有些不同.
我在MySQL中有一个大型数据集data
:
id date val
aaaaa 2021-01-01 TRUE
aaaaa 2021-01-02 FALSE
aaaaa 2021-01-03 FALSE
aaaaa 2021-01-04 TRUE
aaaaa 2021-01-05 FALSE
aaaaa 2021-01-06 TRUE
aaaaa 2021-01-07 FALSE
...
aaaaa 2021-12-31 FALSE
aaaab 2021-01-01 TRUE
aaaab 2021-01-02 FALSE
...
zzzzz 2021-12-31 FALSE
这里,id
是字符串类型的数据,date
的范围是从2021-01-01
到2021-12-31
,没有任何遗漏的天数,val
包含布尔值TRUE
或FALSE
.data
是按id, date
排序的.
我想增加两栏,lagged_date
栏和date_diff
栏.
-
lagged_date
包含id
的先前日期,其中val = TRUE
. -
date_diff
计算该行中date
和lagged_date
之间的天数之差.
理想情况下,我的最终数据集应该如下所示:
id date val lagged_date date_diff
aaaaa 2021-01-01 TRUE NULL NULL
aaaaa 2021-01-02 FALSE 2021-01-01 1
aaaaa 2021-01-03 FALSE 2021-01-01 2
aaaaa 2021-01-04 TRUE 2021-01-01 3
aaaaa 2021-01-05 FALSE 2021-01-04 1
aaaaa 2021-01-06 TRUE 2021-01-04 2
aaaaa 2021-01-07 FALSE 2021-01-06 1
...
aaaaa 2021-12-31 FALSE 2021-12-25 6
aaaab 2021-01-01 TRUE NULL NULL
aaaab 2021-01-02 FALSE 2021-01-01 1
...
(请注意,该数据也是按id, date
排序的)
我try 了以下查询:
SELECT *,
MAX(val) OVER (
PARTITION BY id, val
ORDER BY date
ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
) AS lagged_date,
DATE_DIFF(date, lagged_date, DAY) AS date_diff
FROM data
但lagged_date
并没有产生我想要的输出,只产生了滞后的val
.我也试了MAX(date)
,但没有用.
任何真知灼见都值得赞赏.