Блог

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

Я пишу запрос для Cognos 11, в котором обобщается неделя этих проверок. В настоящее время он сводит все в суммы по классу местоположения. Моя проблема в том, что для отдельных местоположений может быть несколько проверок, и хотя я хочу, чтобы поле отклонения суммировало данные всех проверок независимо от того, является ли это первым подсчетом в этом местоположении, мне нужно только ожидаемое значение для разных местоположений (т. Е. Только Сумма ожидаемого значения, где местоположение отличается).

Ниже приведена упрощенная версия запроса. Возможно ли это вообще или мне придется написать отдельный запрос в Cognos и сделать из него два отчета, которые нужно будет объединить после факта? Как вы, вероятно, можете сказать, я довольно новичок в SQL и Cognos.

 SELECT COALESCE(CASE 
                WHEN location_class = 'A'
                    THEN 'Active'
                WHEN location_class = 'C'
                    THEN 'Active'
                WHEN location_class IN (
                        'R'
                        ,'0'
                        )
                    THEN 'Reserve'
                END, 'Grand Total') "Row Labels"
        ,SUM(NVL(expected_cost, 0)) "Sum of Expected Cost"
        ,SUM(NVL(variance_cost, 0)) "Sum of Variance Cost"
        ,SUM(ABS(NVL(variance_cost, 0))) "Sum of Absolute Cost"
        ,COUNT(DISTINCT location) "Count of Locations"
        ,(SUM(NVL(variance_cost, 0)) / SUM(NVL(expected_cost, 0))) "Variance"
    FROM audit_table
    WHERE audit_datetime <= #prompt('EndDate') # audit_datetime >= #prompt('StartDate') #
    GROUP BY ROLLUP(CASE 
                WHEN location_class = 'A'
                    THEN 'Active'
                WHEN location_class = 'C'
                    THEN 'Active'
                WHEN location_class IN (
                        'R'
                        ,'0'
                        )
                    THEN 'Reserve'
                END)
    ORDER BY 1 ASC
  

Это то, что я надеюсь получить:

Спасибо за любую помощь!

Вы пробовали взглянуть на предложение OVER в SQL? Это позволяет вам использовать оконные функции в результирующем наборе, чтобы вы могли получать агрегированные данные на основе определенных условий. Это, вероятно, помогло бы, поскольку вы, похоже, пытаетесь получить суммирование данных на основе другой группировки в рамках более крупной группировки.

Например, допустим, у нас есть приведенный ниже набор данных:

 group1      group2      val         dateadded
----------- ----------- ----------- -----------------------
1           1           1           2020-11-18
1           1           1           2020-11-20
1           2           10          2020-11-18
1           2           10          2020-11-20
2           3           100         2020-11-18
2           3           100         2020-11-20
2           4           1000        2020-11-18
2           4           1000        2020-11-20
  

Используя один запрос, мы можем вернуть как суммы «val» по «group1», так и суммирование первых (на основе даты и времени) записей «val» в «group2»:

 declare @table table (group1 int, group2 int, val int, dateadded datetime)
insert into @table values (1, 1, 1, getdate())
insert into @table values (1, 1, 1, dateadd(day, 1, getdate()))
insert into @table values (1, 2, 10, getdate())
insert into @table values (1, 2, 10, dateadd(day, 1, getdate()))
insert into @table values (2, 3, 100, getdate())
insert into @table values (2, 3, 100, dateadd(day, 1, getdate()))
insert into @table values (2, 4, 1000, getdate())
insert into @table values (2, 4, 1000, dateadd(day, 1, getdate()))

select t.group1, sum(t.val) as group1_sum, group2_first_val_sum
from @table t
inner join
(
    select group1, sum(group2_first_val) as group2_first_val_sum
    from
    (
        select group1, val as group2_first_val, row_number() over (partition by group2 order by dateadded) as rownumber
        from @table
    ) y
    where rownumber = 1
    group by group1
    
) x on t.group1 = x.group1
group by t.group1, x.group2_first_val_sum
  

Это возвращает приведенный ниже набор результатов:

 group1      group1_sum  group2_first_val_sum
----------- ----------- --------------------
1           22          11
2           2200        1100
  

Самый внутренний подзапрос в объединенной таблице нумерует строки в наборе данных на основе «group2», в результате чего записи будут иметь либо «1», либо «2» в столбце «rownum», поскольку в каждой «group2» всего 2 записи.

Следующий подзапрос принимает эти данные и отфильтровывает все строки, которые не являются первыми (rownum = 1), и суммирует данные «val».

Основной запрос получает сумму значений «val» в каждой «group1» из основной таблицы, а затем присоединяется к таблице с вложенным запросом, чтобы получить сумму значений «val» только для первых записей в каждой «group2».

Есть более эффективные способы написать это, например, перенести суммирование значений «group1» в подзапрос в инструкции SELECT, чтобы избавиться от одного из вложенных табличных подзапросов, но я хотел показать, как это сделать без подзапросов в инструкции SELECT.

Вы пытались поместить distinct внизу вот так?

(СУММА (NVL(variance_cost,0)) / СУММА (NVL(expected_cost,0))) «Отклонение»,

COUNT (ОТДЕЛЬНОЕ местоположение) «Количество местоположений»

ИЗ audit_table