请参阅sql中按时间间隔分组的辅助请求的分布

我们希望看到您的尝试,但从外观上看,您似乎需要将此表视为两个表,并进行基本分组,但必须做到这一点 设想 在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;