我的表中有4列,rii、uii、rdi和udi。如下所示:
+----------+------+----------+------+
| rdi| rii| udi| uii|
+----------+------+----------+------+
|2002-02-06|1376.Q|2002-02-06|1376.Q|
|2002-02-28|1376.Q|2002-02-28|1376.Q|
|2002-03-06|1376.Q|2002-03-06|1376.Q|
|2002-02-01|1792.T|2002-02-01|1792.T|
|2002-03-07|1802.T|2002-03-07|1802.T|
|2002-03-08|1802.T|2002-03-08|1802.T|
|2002-04-03|1802.T|2002-04-03|1802.T|
|2002-03-07|1805.T|2002-03-07|1805.T|
|2002-02-18|1810.T|2002-02-18|1810.T|
|2002-03-22|1821.T|2002-03-22|1821.T|
|2002-02-27|1862.T|2002-02-27|1862.T|
|2002-04-11|1878.T|2002-04-11|1878.T|
|2002-04-18|1884.T|2002-04-18|1884.T|
|2002-02-27|1899.T|2002-02-27|1899.T|
|2002-03-11|1924.T|2002-03-11|1924.T|
|2002-02-05|1925.T|2002-02-05|1925.T|
|2002-01-23|1926.T|2002-01-23|1926.T|
|2002-03-19|1926.T|2002-03-19|1926.T|
|2002-01-25|1942.T|2002-01-25|1942.T|
|2002-01-31|1942.T|2002-01-31|1942.T|
+----------+------+----------+------+
我只想得到一个逻辑上的唯一rii的数量,比如如果我把lookback设为2,那么它应该在一组天内给出唯一的rii的数量(在那个特定的rdi上,在rdi的前两天)
因此,我将回首作为2,我的结果应该是这样的(对于rdi=2002-02-06,它应该在rdi in(2002-02-062002-02-052002-02-04)中找到唯一的rii)
+----------+-------------+----------+------+
| rdi| rii| udi| uii|
+----------+-------------+----------+------+
|2002-02-06|1376.Q,1925.T|2002-02-06|1376.Q|
我试过下面的查询,但没有得到所需的o/p
select count(distinct uii) as u,
rdi,
(select count(distinct rii) from `mytable` where rdi between DATE_SUB(rdi, INTERVAL 2 DAY) AND rdi) as r
from `mytable`
group by rdi
order by rdi;
检查我的小提琴here
最佳答案
您可以使用LEFT JOIN
将每个记录与前几天的记录关联:
select t1.rdi,
group_concat(t2.rii) as rii,
t1.udi,
count(distinct t2.uii)
from `mytable` as t1
left join `mytable` as t2
on t2.rdi between DATE_SUB(t1.rdi, INTERVAL 2 DAY) AND t1.rdi
group by rdi
order by rdi;
输出:
Demo here