时间戳

首页 > 解决方案 > 查看sql中按时间间隔分组的二次请求分布

sql - 查看sql中按时间间隔分组的二次请求分布

问题描述

我有下表：

RequestId,Type, Date,        ParentRequestId
1         1     2020-10-15    null 
2         2     2020-10-19    1 
3         1     2020-10-20    null 
4         2     2020-11-15    3

对于此示例，我对请求类型 1 和 2 感兴趣，以使示例更简单。我的任务是查询一个大数据库，并根据与父事务的日期差异查看二级事务的分布。所以结果看起来像：

Interval,Percentage
0-7 days,50 %
8-15 days,0 %
16-50 days, 50 %

因此，对于预期结果的第一行，我们有 id 2 的请求，对于预期结果的第三行，我们有 id 4 的请求，因为日期差异适合这个间隔。

如何做到这一点？

我正在使用 sql server 2014。

标签： sqlgroupingsql-server-2014

解决方案

我们希望看到您的尝试，但从外观上看，您似乎需要将此表视为 2 个表并执行基本的 GROUP BY，但通过在 CASE 语句上进行分组来使其看起来很有趣。

WITH dateDiffs as (
    /* perform our date calculations first, to get that out of the way */
    SELECT 
      DATEDIFF(Day, parent.[Date], child.[Date]) as daysDiff,
      1 as rowsFound
    FROM    (SELECT RequestID, [Date] FROM myTable WHERE Type = 1) parent
    INNER JOIN  (SELECT ParentRequestID, [Date] FROM myTable WHERE Type = 2) child
    ON parent.requestID = child.parentRequestID
)

/* Now group and aggregate and enjoy your maths! */
SELECT 
  case when daysDiff between 0 and 7 then '0-7'
       when daysDiff between 8 and 15 then '8-15'
       when daysDiff between 16 and 50 THEN '16-50'
       else '50+' 
   end as myInterval,
   sum(rowsFound) as totalFound,
   (select sum(rowsFound) from dateDiffs) as totalRows,
   1.0 * sum(rowsFound) / (select sum(rowsFound) from dateDiffs) * 100.00 as percentFound
FROM dateDiffs
GROUP BY 
   case when daysDiff between 0 and 7 then '0-7'
       when daysDiff between 8 and 15 then '8-15'
       when daysDiff between 16 and 50 THEN '16-50'
       else '50+' 
   end;

推荐阅读