Группировка наборов не агрегирует результаты

Question

Группировка наборов не агрегирует результаты

Я только недавно начал использовать эту функцию, так что простите этот вопрос, если она неуместна.

У меня есть существующий запрос, который, как я понял, мог бы помочь с использованием этого запроса для создания дополнительных групп по группам, сродни промежуточным итогам.

В этом примере я хочу объединиться в иерархию, подобную этой:

Год> Месяц> Центр прибыли> Дата

Пример результатов, которые возвращает этот запрос

Как видите, мои группирующие наборы возвращают ноль. У меня вопрос, почему?

Мой запрос ниже

SELECT 
datepart(yyyy,overview.Date) as 'Year', 
datepart(mm,overview.Date) as'Month',
overview.Date as 'Date',
overview.[Profit Centre],
overview.[Current 
Year Group Bookings],
overview.[Previous Year Group Bookings],
overview.[Current Year Total Covers],
overview.[Previous Year Total Covers],
overview.[Large Group Count]

FROM
(
SELECT 
DISTINCT
CONVERT(date,csd.tendered_date_time) as 'Date',
PCM.profit_center_name as 'Profit Centre',
PCD.profit_center_id,
(
SELECT count(csd2.num_covers)
FROM ig_Business..check_sales_detail csd2
INNER JOIN ig_Dimension..Profit_Center_Dimension PCD2 (NOLOCK) ON 
PCD2.profit_center_dim_id = CSD2.profit_center_dim_id and PCD2.ent_id = 1
WHERE PCD2.profit_center_id = PCD.profit_center_id
AND CONVERT(date,CSD2.tendered_date_time) = 
CONVERT(date,CSD.tendered_date_time)
AND CSD2.num_covers >= 
(CASE 
WHEN PCD.profit_center_id = '77' THEN '8'
WHEN PCD.profit_center_id = '13' THEN '10'
WHEN PCD.profit_center_id IN ('60','61','62','63','64','65') THEN '16'
WHEN PCD.profit_center_id = '14' THEN '10'
WHEN PCD.profit_center_id = '90' THEN '10'
WHEN PCD.profit_center_id = '98' THEN '8' 
WHEN PCD.profit_center_id IN ('74','101') THEN '10'
WHEN PCD.profit_center_id = '194' THEN '10'
WHEN PCD.profit_center_id = '49' THEN '15'
WHEN PCD.profit_center_id IN ('20','21','22','200') THEN '8'
WHEN PCD.profit_center_id = '74' THEN '15'
WHEN PCD.profit_center_id = '26' THEN '10'
WHEN PCD.profit_center_id IN ('86','68') THEN '8'
WHEN PCD.profit_center_id IN ('40','41','42','43') THEN '8'
WHEN PCD.profit_center_id IN ('96','98','99') THEN '10'
WHEN PCD.profit_center_id = '57' THEN '10'
END)
) as 'Current Year Group Bookings',
(
SELECT count(csd2.num_covers)
FROM ig_Business..check_sales_detail csd2
INNER JOIN ig_Dimension..Profit_Center_Dimension PCD2 (NOLOCK) ON 
PCD2.profit_center_dim_id = CSD2.profit_center_dim_id and PCD2.ent_id = 1
WHERE PCD2.profit_center_id = PCD.profit_center_id
AND CONVERT(date,CSD2.tendered_date_time) = 
CONVERT(date,DATEADD(month,-12,CSD.tendered_date_time))
AND CSD2.num_covers >= 
(CASE 
WHEN PCD.profit_center_id = '77' THEN '8'
WHEN PCD.profit_center_id = '13' THEN '10'
WHEN PCD.profit_center_id IN ('60','61','62','63','64','65') THEN '16'
WHEN PCD.profit_center_id = '14' THEN '10'
WHEN PCD.profit_center_id = '90' THEN '10'
WHEN PCD.profit_center_id = '98' THEN '8' 
WHEN PCD.profit_center_id IN ('74','101') THEN '10'
WHEN PCD.profit_center_id = '194' THEN '10'
WHEN PCD.profit_center_id = '49' THEN '15'
WHEN PCD.profit_center_id IN ('20','21','22','200') THEN '8'
WHEN PCD.profit_center_id = '74' THEN '15'
WHEN PCD.profit_center_id = '26' THEN '10'
WHEN PCD.profit_center_id IN ('86','68') THEN '8'
WHEN PCD.profit_center_id IN ('40','41','42','43') THEN '8'
WHEN PCD.profit_center_id IN ('96','98','99') THEN '10'
WHEN PCD.profit_center_id = '57' THEN '10'
END)
) as 'Previous Year Group Bookings',

(
SELECT ISNULL(SUM(csd2.num_covers),0)
FROM ig_Business..check_sales_detail csd2
INNER JOIN ig_Dimension..Profit_Center_Dimension PCD2 (NOLOCK) ON 
PCD2.profit_center_dim_id = CSD2.profit_center_dim_id and PCD2.ent_id = 1
WHERE PCD2.profit_center_id = PCD.profit_center_id
AND CONVERT(date,CSD2.tendered_date_time) = 
CONVERT(date,CSD.tendered_date_time)
AND CSD2.num_covers >= 
(CASE 
WHEN PCD.profit_center_id = '77' THEN '8'
WHEN PCD.profit_center_id = '13' THEN '10'
WHEN PCD.profit_center_id IN ('60','61','62','63','64','65') THEN '16'
WHEN PCD.profit_center_id = '14' THEN '10'
WHEN PCD.profit_center_id = '90' THEN '10'
WHEN PCD.profit_center_id = '98' THEN '8' 
WHEN PCD.profit_center_id IN ('74','101') THEN '10'
WHEN PCD.profit_center_id = '194' THEN '10'
WHEN PCD.profit_center_id = '49' THEN '15'
WHEN PCD.profit_center_id IN ('20','21','22','200') THEN '8'
WHEN PCD.profit_center_id = '74' THEN '15'
WHEN PCD.profit_center_id = '26' THEN '10'
WHEN PCD.profit_center_id IN ('86','68') THEN '8'
WHEN PCD.profit_center_id IN ('40','41','42','43') THEN '8'
WHEN PCD.profit_center_id IN ('96','98','99') THEN '10'
WHEN PCD.profit_center_id = '57' THEN '10'
END)

) as 'Current Year Total Covers',


(
SELECT ISNULL(SUM(csd2.num_covers),0)
FROM ig_Business..check_sales_detail csd2
INNER JOIN ig_Dimension..Profit_Center_Dimension PCD2 (NOLOCK) ON 
PCD2.profit_center_dim_id = CSD2.profit_center_dim_id and PCD2.ent_id = 1
WHERE PCD2.profit_center_id = PCD.profit_center_id
AND CONVERT(date,CSD2.tendered_date_time) = 
CONVERT(date,DATEADD(month,-12,CSD.tendered_date_time))
AND CSD2.num_covers >= 
(CASE 
WHEN PCD.profit_center_id = '77' THEN '8'
WHEN PCD.profit_center_id = '13' THEN '10'
WHEN PCD.profit_center_id IN ('60','61','62','63','64','65') THEN '16'
WHEN PCD.profit_center_id = '14' THEN '10'
WHEN PCD.profit_center_id = '90' THEN '10'
WHEN PCD.profit_center_id = '98' THEN '8' 
WHEN PCD.profit_center_id IN ('74','101') THEN '10'
WHEN PCD.profit_center_id = '194' THEN '10'
WHEN PCD.profit_center_id = '49' THEN '15'
WHEN PCD.profit_center_id IN ('20','21','22','200') THEN '8'
WHEN PCD.profit_center_id = '74' THEN '15'
WHEN PCD.profit_center_id = '26' THEN '10'
WHEN PCD.profit_center_id IN ('86','68') THEN '8'
WHEN PCD.profit_center_id IN ('40','41','42','43') THEN '8'
WHEN PCD.profit_center_id IN ('96','98','99') THEN '10'
WHEN PCD.profit_center_id = '57' THEN '10'
END)
) as 'Previous Year Total Covers',


(CASE 
WHEN PCD.profit_center_id = '77' THEN '8'
WHEN PCD.profit_center_id = '13' THEN '10'
WHEN PCD.profit_center_id IN ('60','61','62','63','64','65') THEN '16'
WHEN PCD.profit_center_id = '14' THEN '10'
WHEN PCD.profit_center_id = '90' THEN '10'
WHEN PCD.profit_center_id = '98' THEN '8' 
WHEN PCD.profit_center_id IN ('74','101') THEN '10'
WHEN PCD.profit_center_id = '194' THEN '10'
WHEN PCD.profit_center_id = '49' THEN '15'
WHEN PCD.profit_center_id IN ('20','21','22','200') THEN '8'
WHEN PCD.profit_center_id = '74' THEN '15'
WHEN PCD.profit_center_id = '26' THEN '10'
WHEN PCD.profit_center_id IN ('86','68') THEN '8'
WHEN PCD.profit_center_id IN ('40','41','42','43') THEN '8'
WHEN PCD.profit_center_id IN ('96','98','99') THEN '10'
WHEN PCD.profit_center_id = '57' THEN '10'
END) AS 'Large Group Count'


FROM ig_business..Check_Sales_Detail CSD (NOLOCK) 
INNER JOIN ig_Dimension..Profit_Center_Dimension PCD (NOLOCK) ON 
PCD.profit_center_dim_id = CSD.profit_center_dim_id and PCD.ent_id = 1
INNER JOIN it_cfg..Profit_Center_Master PCM (NOLOCK) ON PCM.profit_center_id 
= PCD.profit_center_id and PCm.ent_id = 1 
WHERE  
 ( 
  (pcd.profit_center_id = '77' AND csd.num_covers > 8)      
OR
  (pcd.profit_center_id = '13' AND csd.num_covers >= 10)        
OR
  (pcd.profit_center_id IN ('60','61','62','63','64','65') AND 
csd.num_covers > 16) 
OR
  (pcd.profit_center_id = 14 AND csd.num_covers >= 10)      
OR
  (pcd.profit_center_id = 90 AND csd.num_covers >= 10)
OR
  (pcd.profit_center_id = 98 AND csd.num_covers >= 8)
OR
  (pcd.profit_center_id IN ('74','101') AND csd.num_covers >= 10)
OR
  (pcd.profit_center_id = '194' AND csd.num_covers >= 10)
OR
  (pcd.profit_center_id = '49' AND csd.num_covers >= 15)
OR
  (pcd.profit_center_id IN ('20','21','22','200') AND csd.num_covers >= 8)
OR
  (pcd.profit_center_id = '74' AND csd.num_covers >= 15)
OR
  (pcd.profit_center_id = '26' AND csd.num_covers >= 10)
OR    
  (pcd.profit_center_id IN ('86','68') AND csd.num_covers >= 8)
OR
  (pcd.profit_center_id IN ('40','41','42','43') AND csd.num_covers >= 8)
OR
  (pcd.profit_center_id IN ('96','98','99') AND csd.num_covers >= 10)
OR
  (pcd.profit_center_id = '57' AND csd.num_covers >= 10)
)  
AND
CSD.tendered_date_time > CONVERT(date,'2017-01-01') -- We only want 
comparisons from today going forward, i.e. not historical


GROUP BY 
GROUPING SETS
(
(csd.tendered_date_time, PCM.profit_center_name,PCD.profit_center_id),
(datepart(yyyy,csd.tendered_date_time),datepart(mm,csd.tendered_date_time), 
pcm.profit_center_name),
()

)


) overview

ORDER BY overview.[Profit Centre] asc, CONVERT(date,overview.Date) asc

0

sql sql-server t-sql sql-server-2008 grouping-sets

Источник

user1470741 16 янв '18 в 00:45

1 ответ

Решение

Другие вопросы по тегам sql sql-server t-sql sql-server-2008 grouping-sets

user2067753 16 янв '18 в 04:37 2018-01-16 04:37 · Accepted Answer · 2018-01-16 04:37

У меня вопрос, почему (групповые множества равны нулю)?

Потому что вы не используете агрегатные функции, такие как SUM() или же COUNT() таким образом, что можно суммировать в промежуточные итоги. Вы используете набор "коррелированных подзапросов", которые используют COUNT() но к моменту их оценки внешний запрос просто видит эти столбцы как данные (а не как агрегаты).

пример (не проверенный конечно):

WITH pcd AS (
      SELECT *
      FROM ig_Dimension..Profit_Center_Dimension pcd
      WHERE (
            OR (pcd.profit_center_id = '13' AND csd.num_covers >= 10)
            OR (pcd.profit_center_id = 14 AND csd.num_covers >= 10)   -- is it an integer? or not?
            OR (pcd.profit_center_id = '26' AND csd.num_covers >= 10)
            OR (pcd.profit_center_id = '49' AND csd.num_covers >= 15)
            OR (pcd.profit_center_id = '57' AND csd.num_covers >= 10)
            OR (pcd.profit_center_id = '74' AND csd.num_covers >= 15)
            OR (pcd.profit_center_id = '77' AND csd.num_covers > 8)
            OR (pcd.profit_center_id = 90 AND csd.num_covers >= 10)   -- is it an integer? or not?
            OR (pcd.profit_center_id = 98 AND csd.num_covers >= 8)    -- is it an integer? or not?
            OR (pcd.profit_center_id = '194' AND csd.num_covers >= 10)
            OR (pcd.profit_center_id IN ('20', '21', '22', '200') AND csd.num_covers >= 8)
            OR (pcd.profit_center_id IN ('40', '41', '42', '43')  AND csd.num_covers >= 8)
            OR (pcd.profit_center_id IN ('60', '61', '62', '63', '64', '65') AND csd.num_covers > 16)
            OR (pcd.profit_center_id IN ('74', '101')      AND csd.num_covers >= 10)
            OR (pcd.profit_center_id IN ('86', '68')       AND csd.num_covers >= 8)
            OR (pcd.profit_center_id IN ('96', '98', '99') AND csd.num_covers >= 10)
            )
      AND pcd.ent_id = 1
      )
SELECT
      DATEPART(yyyy, csd.tendered_date_time) AS 'Year'
    , DATEPART(mm, csd.tendered_date_time)   AS 'Month'
    , pcd.[Profit Centre]
    , pcm.profit_center_name                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               AS 'Profit Centre'
    , pcd.profit_center_id
    , COUNT(csd.num_covers)
FROM ig_business..Check_Sales_Detail csd
INNER JOIN pcd ON pcd.profit_center_dim_id = csd.profit_center_dim_id
INNER JOIN it_cfg..Profit_Center_Master pcm ON pcm.profit_center_id = pcd.profit_center_id
      AND pcm.ent_id = 1
GROUP BY GROUPING SETS
    (  DATEPART(yyyy, csd.tendered_date_time) AS 'Year'
    , DATEPART(mm, csd.tendered_date_time)   AS 'Month'
    , pcd.[Profit Centre]
    , pcm.profit_center_name                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               AS 'Profit Centre'
    , pcd.profit_center_id
    )

ПРИМЕЧАНИЕ: вы должны быть осторожны с "неявными преобразованиями", если pcd.profit_center_id является целым числом, тогда НЕ используйте "194", но если это varchar или подобное, то не используйте целые числа. Я также предлагаю вам попытаться сохранить положение where в некотором "порядке", чтобы его было легче читать.