开发者社区> 问答> 正文

计算特定时间范围之间的访问次数

计算特定时间范围之间的访问次数

展开
收起
贺贺_ 2019-12-02 22:37:46 445 0
1 条回答
写回答
取消 提交回答
  • ;WITH CTE AS (
    
    SELECT 
    substring(convert(varchar(20), GeneratedAt, 9), 13, 5) + ' ' + 
    substring(convert(varchar(30), GeneratedAt, 9), 25, 2) AS GetTime
    FROM #Temp
    
    )
    
    

    在这里,我转换为时间值作为AM/PM

    SELECT * FROM
    ( 
    SELECT '7 AM' AS TimeFrame,SUM(CASE WHEN GetTime BETWEEN Convert(datetime,'7 AM') AND Convert(datetime,'10 AM') THEN 1 ELSE 0 END) AS COUNT FROM CTE
    UNION 
    SELECT '10 AM' AS TimeFrame,SUM(CASE WHEN GetTime BETWEEN Convert(datetime,'10 AM') AND Convert(datetime,'1 PM') THEN 1 ELSE 0 END) AS COUNT FROM CTE 
    UNION 
    SELECT '1 PM' AS TimeFrame,SUM(CASE WHEN GetTime BETWEEN Convert(datetime,'1 PM') AND Convert(datetime,'4 PM') THEN 1 ELSE 0 END) AS COUNT FROM CTE 
    UNION 
    SELECT '4 PM' AS TimeFrame,SUM(CASE WHEN GetTime BETWEEN Convert(datetime,'4 PM') AND Convert(datetime,'7 PM') THEN 1 ELSE 0 END) AS COUNT FROM CTE
    UNION 
    SELECT '7 PM' AS TimeFrame,SUM(CASE WHEN GetTime BETWEEN Convert(datetime,'7 PM') AND Convert(datetime,'10 PM') THEN 1 ELSE 0 END) AS COUNT FROM CTE
    UNION 
    SELECT '10 PM' AS TimeFrame,SUM(CASE WHEN GetTime BETWEEN Convert(datetime,'10 PM') AND Convert(datetime,'1 AM') THEN 1 ELSE 0 END) AS COUNT FROM CTE
    )a 
    ORDER BY Convert(datetime,a.TimeFrame)
    
    

    而不是硬代码值使用可以使用@Parameter或其他东西...根据您的喜好

    2019-12-02 22:38:17
    赞同 展开评论 打赏
问答地址:
问答排行榜
最热
最新

相关电子书

更多
用计算和数据去改变整个世界 立即下载
重新定义计算的边界 立即下载
数据带来无限可能 立即下载