我有一个带有日期戳和ID号的数据集，如下所示：
Date_Stamp | ID
2013-07-17 | ID1
2013-07-17 | ID1
2013-08-19 | ID1
2013-08-19 | ID2
从此数据中，我需要填充一个包含以下字段的表：日期，ID，First_attempt，Second_Attempt，Third_Attempt，Total_Attempts。
有了这些数据，文件将如下所示：
Date | ID | First_attempt | Second_Attempt | Third_Attempt | Total_Attempts
2013-07-17 | ID1 | 1 | 1 | 0 | 2
2013-08-19 | ID1 | 0 | 0 | 1 | 1
2013-08-19 | ID2 | 1 | 0 | 0 | 1
我已经可以使用以下代码进行首次尝试：
Select min(date_stamp) as date_stamp,
ID,
1 as First_attempt,
0 as Second_Attempt,
0 as Third_Attempt,
0 as Total_Attempts
from Table
group by ID
和总尝试次数（每天），包含以下代码：
Select date_stamp as date_stamp,
ID,
0 as First_attempt,
0 as Second_Attempt,
0 as Third_Attempt,
count(ID) as Total_Attempts
from Table
group by date_stamp, ID
有人可以帮您编写第二次和第三次尝试的脚本吗？
;WITH TEMPTABLE AS(
SELECT DATE_STAMP,
ID,
ROW_NUMBER() OVER( PARTITION BY ID ORDER BY DATE_STAMP ) AS ROWNUMBER,
Count(ID) OVER(PARTITION BY Date_Stamp, ID) as countID
FROM #temp)
SELECT DATE_STAMP,
ID,
MAX(CASE WHEN ROWNUMBER = 1 THEN 1 ELSE 0 END )AS FIRST_ATTEMPT,
MAX(CASE WHEN ROWNUMBER = 2 THEN 1 ELSE 0 END) AS SECOND_ATTEMPT,
MAX(CASE WHEN ROWNUMBER = 3 THEN 1 ELSE 0 END) AS THIRD_ATTEMPT,
MAX(countID) Total_Attempts
FROM TEMPTABLE
GROUP BY DATE_STAMP,ID
本文收集自互联网，转载请注明来源。
如有侵权，请联系[email protected] 删除。
我来说两句