如何将结果分成单独的小时

Question

我需要此查询将小时作为单独的列，并统计该小时内生成的每个分数范围的案例数量。我现在得到的是一个日期列和一个小时列，但每个小时有多行。

SELECT date(s.CREATED_DTTM) as date,
   hour(s.CREATED_DTTM) as hour 
   ,COUNT(score) AS TOTAL                        
   ,SUM(CASE WHEN s.score = 000 THEN 1 ELSE 0 END)       
    AS #000 
   ,SUM(CASE WHEN s.score BETWEEN 001 AND 050 THEN 1 ELSE 0 END)       
    AS #001_TO_050                                                       
   ,SUM(CASE WHEN s.score BETWEEN 051 AND 100 THEN 1 ELSE 0 END)       
    AS #051_TO_100                                                       
   ,SUM(CASE WHEN s.score BETWEEN 101 AND 150 THEN 1 ELSE 0 END)       
    AS #101_TO_150                                                       
   ,SUM(CASE WHEN s.score BETWEEN 151 AND 200 THEN 1 ELSE 0 END)       
    AS #151_TO_200                                                       
   ,SUM(CASE WHEN s.score BETWEEN 201 AND 250 THEN 1 ELSE 0 END)       
    AS #201_TO_250                                                       
   ,SUM(CASE WHEN s.score BETWEEN 251 AND 300 THEN 1 ELSE 0 END)       
    AS #251_TO_300                                                       
   ,SUM(CASE WHEN s.score BETWEEN 301 AND 350 THEN 1 ELSE 0 END)       
    AS #301_TO_350                                                       
   ,SUM(CASE WHEN s.score BETWEEN 351 AND 400 THEN 1 ELSE 0 END)       
    AS #351_TO_400                                                       
   ,SUM(CASE WHEN s.score BETWEEN 401 AND 450 THEN 1 ELSE 0 END)       
    AS #401_TO_450                                                       
   ,SUM(CASE WHEN s.score BETWEEN 451 AND 500 THEN 1 ELSE 0 END)       
    AS #451_TO_500                                                       
   ,SUM(CASE WHEN s.score BETWEEN 501 AND 550 THEN 1 ELSE 0 END)       
     AS #501_TO_550                                                    
    ,SUM(CASE WHEN s.score BETWEEN 551 AND 600 THEN 1 ELSE 0 END)    
     AS #551_TO_600                                                    
    ,SUM(CASE WHEN s.score BETWEEN 601 AND 650 THEN 1 ELSE 0 END)    
     AS #601_TO_650                                                    
    ,SUM(CASE WHEN s.score BETWEEN 651 AND 700 THEN 1 ELSE 0 END)    
     AS #651_TO_700                                                    
    ,SUM(CASE WHEN s.score BETWEEN 701 AND 750 THEN 1 ELSE 0 END)    
     AS #701_TO_750                                                    
    ,SUM(CASE WHEN s.score BETWEEN 751 AND 800 THEN 1 ELSE 0 END)    
     AS #751_TO_800                                                    
    ,SUM(CASE WHEN s.score BETWEEN 801 AND 850 THEN 1 ELSE 0 END) 
     AS #801_TO_850  
     ,SUM(CASE WHEN s.score BETWEEN 851 AND 900 THEN 1 ELSE 0 END) 
     AS #851_TO_900 
     ,SUM(CASE WHEN s.score BETWEEN 901 AND 950 THEN 1 ELSE 0 END) 
     AS #901_TO_950 
      ,SUM(CASE WHEN s.score BETWEEN 951 AND 1000 THEN 1 ELSE 0 END) 
     AS #951_TO_1000 
     ,s.OID,
     CASE s.Type when '1' then 'a1' ELSE 'a2' END as "TYPE"
     FROM Schema.Table 1 s, Schema.Table 2 c                                             
     WHERE s.CREATED_DTTM >= '2022-03-01-00.00.00.000001' 
     AND  s.CREATED_DTTM <  '2022-03-02-00.00.00.000000' 
     and s.ID = c.ID
     and s.OID = 'OID'
     GROUP BY  s.Type, s.OID,  date(s.CREATED_DTTM), hour(s.CREATED_DTTM), s.ID
     ORDER BY  date(s.CREATED_DTTM) desc, hour(s.CREATED_DTTM) desc
     WITH UR;
     ```

这是我将从上述查询中获得的结果集的一小部分；但是，正如您所看到的，我在当天的 23 小时 (23:00) 有多个条目。

DATE       HOUR TOTAL   #000    #001_TO_050     #051_TO_100     #101_TO_150    OID    TYPE
2022-03-01  23    1       0          0               1               0          1       a1
2022-03-01  23    1       0          0               0               0          1       a2
2022-03-01  23    1       0          0               0               0          1       a2
2022-03-01  23    1       0          0               0               0          2       a1
2022-03-01  23    1       0          0               0               0          1       a1