sql - 我有一张桌子,我想在上面做一些预先计算并让桌子变成新的形状
问题描述
ID input_date created
22564 3/4/2019 5/29/2019
22564 3/4/2019 3/6/2019
22564 3/4/2019 3/12/2019
22564 3/4/2019 4/18/2019
22564 3/4/2019 4/12/2019
22564 3/4/2019 4/10/2019
22564 3/4/2019 3/25/2019
22564 3/4/2019 3/29/2019
22564 3/4/2019 4/4/2019
22564 3/4/2019 3/19/2019
22564 3/4/2019 5/29/2019
22564 3/4/2019 5/20/2019
22543 3/4/2019 3/20/2019
22543 3/4/2019 3/28/2019
22543 3/4/2019 4/12/2019
22543 3/4/2019 4/19/2019
22543 3/4/2019 3/13/2019
22543 3/4/2019 3/6/2019
22543 3/4/2019 5/20/2019
22543 3/4/2019 6/17/2019
22543 3/4/2019 4/4/2019
12558 3/4/2019 1/4/2019
我想先得到这个
acctrefno first second third fourth
22564 2 8 15 21
22543 2 9 16 24
22595 2 9 16 24
24324 1 7 12 19
26506 1 12 20 26
最后,我想到达这里,即
- 如果第一个是在日期输入后的 3 天内,我想坑 1
- 如果第二个是从第一个开始的 3 天,那么我想放 1
- 否则为 0,依此类推,直到第四个日期。
ETC...
acctrefno first second third fourth
22564 1 0 0 0
22543 1 0 0 0
22595 1 0 0 0
24324 1 0 0 0
26506 1 0 0 0
我已经在 excel 和 Tableau 中完成了这项工作,但它非常耗时,而且我有一个大数据集,所以我需要在 SQL 中完成
没有代码,因为我没有在 SQL 中尝试过瘦我不知道如何去做。我唯一能做的就是使用datediff
函数并想出天数
不适用
解决方案
计算 datediff,按 row_number() 和链差异阈值标志进行透视,以便如果前一个标志为 0,则其余标志也为 0。如果您不想链接标志只是select ID, f1 first, f2 second, f3 third, f4 fourth
.
select ID, f1 first, f1*f2 second, f1*f2*f3 third, f1*f2*f3*f4 fourth
from(
select ID, [1],[2],[3],[4]
from(
select ID, input_date, datediff(dd, input_date, created) d
, row_number() over(partition by ID order by created) rn
from ta
) t
pivot (max(d) for rn in ([1],[2],[3],[4])) p
) t
cross apply(
select f1 = case when [1] <= 3 then 1 else 0 end
,f2 = case when [2]-[1] <= 3 then 1 else 0 end
,f3 = case when [3]-[2] <= 3 then 1 else 0 end
,f4 = case when [4]-[3] <= 3 then 1 else 0 end
) flags;
推荐阅读
- javascript - 仅在 IE11 中定期为我的网站显示空白白页
- python - 弹出窗口不起作用,但没有显示错误(Kivy)
- docker - nginx-proxy+docker swarm => 负载均衡器不工作
- docker - Docker Windows Network Routing
- java - Jpa search by child property does not fetch all childs
- spring-integration - Spring Batch Partition Jobs Configuration On WildFly 18.x / JBoss EAP 7.x
- typescript - How to force interface key of certain enum type?
- java - elasticsearch generate query using resthighlevel client
- flutter - how to get geolocation data(latitude, longitude) correctly from firebase (flutter/dart)?
- sonarqube - Is there any possible way to add the custom created jar plugin to SonarQube in UI?