SQL Server -按组表达式选择结果，但根据另一个组表达式对其进行过滤

Question

我正在处理一个问题，其中我需要根据某个标准聚合数据。具体情况如下：

我在数据库中有一个应用程序的消耗数据，我需要将其显示在表格中。人们可以根据分钟、小时、日和月来聚合数据。例如，当人们按分钟聚合数据时，数据将显示在一个表中，最多显示10行(但这里是棘手的部分，在本例中这些行是基于小时的)。因此，不是说我想要(分钟聚合的)前10行，我必须找出与显示10个不同小时的结果相等的条目数量。

我已经尝试进行查询，现在我是为了更好地说明问题(查询不会运行)：

SELECT  count(*) as COUNT,
  year([CONSUMPTION_DATE]) as YEAR_BY_MINUTE, 
  month([CONSUMPTION_DATE]) as MONTH_BY_MINUTE, 
  day([CONSUMPTION_DATE]) as DAY_BY_MINUTE,
  DATEPART ( hour , [CONSUMPTION_DATE] ) as HOUR_BY_MINUTE,
  DATEPART ( minute , [CONSUMPTION_DATE]) as MINUTE_BY_MINUTE
  FROM [CONSUMPTION_TABLE] 
  where (YEAR_BY_MINUTE, MONTH_BY_MINUTE, DAY_BY_MINUTE, HOUR_BY_MINUTE) IN 

        (select top 10  year([CONSUMPTION_DATE]) as YEAR_BY_HOUR, 
            month([CONSUMPTION_DATE])as MONTH_BY_HOUR, 
            day([CONSUMPTION_DATE]) as DAY_BY_HOUR,
            DATEPART ( hour , [CONSUMPTION_DATE] ) as HOUR_BY_HOUR
            FROM [CONSUMPTION_TABLE]
            where [CONSUMPTION_DATE] between '2018-01-18' and '2018-03-20'
            GROUP BY year([CONSUMPTION_DATE]), 
                     month([CONSUMPTION_DATE]), 
                     day([CONSUMPTION_DATE]),
                     DATEPART ( hour , [CONSUMPTION_DATE])
        )

  GROUP BY  year([CONSUMPTION_DATE]), 
  month([CONSUMPTION_DATE]), 
  day([CONSUMPTION_DATE]),
  DATEPART ( hour , [CONSUMPTION_DATE] ),
  DATEPART ( minute , [CONSUMPTION_DATE] )

我也一直在尝试另一种方法，但查询运行时出现了错误：

;with MinuteGroup as 
        (
            select  year([CONSUMPTION_DATE]) as YEAR_GROUP_MINUTE, 
                    month([CONSUMPTION_DATE])as MONTH_GROUP_MINUTE, 
                    day([CONSUMPTION_DATE]) as DAY_GROUP_MINUTE,
                    DATEPART ( hour , [CONSUMPTION_DATE] ) as HOUR_GROUP_MINUTE,
                    DATEPART ( minute , [CONSUMPTION_DATE] ) as MINUTE_GROUP_MINUTE
                FROM [CONSUMPTION_TABLE]
                where [CONSUMPTION_DATE] between '2018-01-18' and '2018-03-20'
                group by    year([CONSUMPTION_DATE]), 
                            month([CONSUMPTION_DATE]), 
                            day([CONSUMPTION_DATE]),
                            DATEPART ( hour , [CONSUMPTION_DATE] ),
                            DATEPART ( minute , [CONSUMPTION_DATE] )
        )
    select * from MinuteGroup where 
        (YEAR_GROUP_MINUTE, MONTH_GROUP_MINUTE, DAY_GROUP_MINUTE, HOUR_GROUP_MINUTE) 
            in (    select  year([CONSUMPTION_DATE]), 
                            month([CONSUMPTION_DATE]), 
                            day([CONSUMPTION_DATE]) ,
                            DATEPART ( hour , [CONSUMPTION_DATE] ) 
                    FROM [CONSUMPTION_TABLE]
                    where [CONSUMPTION_DATE] between '2018-01-18' and '2018-03-20'
                    group by year([CONSUMPTION_DATE]), 
                             month([CONSUMPTION_DATE]), 
                             day([CONSUMPTION_DATE]),
                             DATEPART ( hour , [CONSUMPTION_DATE])   
                )

是否有另一种更容易的方法来实现这一点，或者我必须更改查询中的哪些内容才能使它们正常工作？

PS:我将不得不把这个转移到HQL，所以如果它会更容易，我也会接受这个答案。

编辑:因为很难想象我在这里想要实现的是一张图片：

​

(我汇总的单位时间将是1,2,3,4,5，...)

Answer 1

经过一段时间的搜索和利用其他解决方案，我提出了一个sql查询，在这里我可以得到想要的结果，所以我认为在这里发布它会很好，这样你就可以更好地了解问题，并在遇到同样情况时帮助你。最后一个查询保留为：

;with MinuteGroup as 
        (
            select  count(*) as COUNT,
                    year([CONSUMPTION_DATE]) as YEAR_GROUP_MINUTE, 
                    month([CONSUMPTION_DATE])as MONTH_GROUP_MINUTE, 
                    day([CONSUMPTION_DATE]) as DAY_GROUP_MINUTE,
                    DATEPART ( hour , [CONSUMPTION_DATE] ) as HOUR_GROUP_MINUTE,
                    DATEPART ( minute , [DATE_SELECTION_START] ) as MINUTE_GROUP_MINUTE
                FROM [CONSUMPTION_TABLE]
                where [CONSUMPTION_DATE] between '2018-01-18' and '2018-03-20'
                group by    year([CONSUMPTION_DATE]), 
                            month([CONSUMPTION_DATE]), 
                            day([CONSUMPTION_DATE]),
                            DATEPART ( hour , [CONSUMPTION_DATE] ),
                            DATEPART ( minute , [CONSUMPTION_DATE] )
        ),
        HourGroup as 
        (
            select top 10 year([CONSUMPTION_DATE]) as YEAR_GROUP_MINUTE, 
                    month([CONSUMPTION_DATE])as MONTH_GROUP_MINUTE, 
                    day([CONSUMPTION_DATE]) as DAY_GROUP_MINUTE,
                    DATEPART ( hour , [CONSUMPTION_DATE] ) as HOUR_GROUP_MINUTE
                FROM [CONSUMPTION_TABLE]
                where [CONSUMPTION_DATE] between '2018-01-18' and '2018-03-20'
                group by    year([CONSUMPTION_DATE]), 
                            month([CONSUMPTION_DATE]), 
                            day([CONSUMPTION_DATE]),
                            DATEPART ( hour , [CONSUMPTION_DATE] )
        )
    select  MinuteGroup.COUNT,
            MinuteGroup.YEAR_GROUP_MINUTE,
            MinuteGroup.MONTH_GROUP_MINUTE,
            MinuteGroup.DAY_GROUP_MINUTE,
            MinuteGroup.HOUR_GROUP_MINUTE,
            MinuteGroup.MINUTE_GROUP_MINUTE
    from MinuteGroup join HourGroup 
                on  HourGroup.YEAR_GROUP_MINUTE = MinuteGroup.YEAR_GROUP_MINUTE
                and HourGroup.MONTH_GROUP_MINUTE = MinuteGroup.MONTH_GROUP_MINUTE
                and HourGroup.DAY_GROUP_MINUTE = MinuteGroup.DAY_GROUP_MINUTE
                and HourGroup.HOUR_GROUP_MINUTE = MinuteGroup.HOUR_GROUP_MINUTE

就像这样，我有20行图像，而不是图像的前10行，这相当于10个小时：

​

​