Суммирование продаж по разным годам

    | Product ID | YearBought | Sales | Min_Year | Max_Year |
    |      1     |    2016    |  $20  |   2011   |   2016   |
    |      2     |    2016    |  $10  |   2016   |   2018   |
    |      2     |    2017    |  $30  |   2016   |   2018   |
    |      3     |    2017    |  $5   |   2015   |   2019   |
    |      3     |    2018    |  $10  |   2015   |   2019   |
    |      3     |    2018    |  $20  |   2015   |   2019   |
    |      3     |    2019    |  $5   |   2015   |   2019   |
    |      3     |    2019    |  $30  |   2015   |   2019   |
    |      4     |    2018    |  $5   |   2018   |   2020   |
    |      4     |    2019    |  $10  |   2018   |   2020   |
    |      4     |    2020    |  $20  |   2018   |   2020   |

Min_Year = год, когда продукт был впервые представлен

Max_Year + 1 = год возврата товара

Выше приведен пример таблицы, с которой я работаю. Пытаясь найти:

  • сумма продаж новых продуктов, привезенных в год, когда они были впервые представлены

  • сумма «упавших продаж», также известная как сумма продаж продуктов через год после того, как они упали (не было продаж). (Например, продукт принес 15 долларов в 2018 году, но не имел продаж в 2019 году, хочу показать 15 долларов как падение продаж в 2019 году)

Ожидаемый результат:

    |     YearBought   | New Product Sales | Dropped Product Sales | 
    |      2016        |        $10        |                       | 
    |      2017        |                   |           $20         |  
    |      2018        |         $5        |                       |  
    |      2019        |                   |                       |  
    |      2020        |                   |           $35         |

думал что-то подобное, но это не работает. любая помощь будет оценена!

select 
    YearBought,
    sum(case when yearbought=min_year then sales else 0 end) as NewSales,
    sum(case when yearbought=max_year+1 then sales else 0 end) as DropSales
from 
    #t 
group by 
    yearbought

sql sql-server sql-server-2019 group-by case-when
person Er In    schedule 18.03.2020    source источник
comment
Дайте определение «не работает». Ошибка? Неверный вывод? какие? Почему ваши выходные данные не включают 10 долларов США от продукта 2 за 2016 год в столбце продаж нового продукта? Это ошибка с вашей стороны или есть какая-то другая логика, которую вы не разделяли?   -  person urdearboy    schedule 18.03.2020
comment
@urdearboy, вы правы, новые продажи = 10 долларов в 2016 году. У меня есть куча продуктов и лет в моем реальном наборе данных. Ошибка из приведенного выше кода показывает мне 0 долларов США при продажах удаленных продуктов, хотя это дает мне правильные новые продажи. считаю, что я поделился всей информацией, пожалуйста, дайте мне знать, если будет полезно больше   -  person Er In    schedule 18.03.2020

Ответы (3)


arrow_upward
0
arrow_downward

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

with

    droppedProds as (

        select      droppedYear = yearBought + 1, 
                    foregoneSales = sum(sales)
        from        @t t 
        where       YearBought = Max_Year
        group by    YearBought

    ),

    newSales as (

        select      YearBought,
                    sales = sum(sales),
                    newSales = sum(case when yearBought = min_year then sales end)
        from        @t t
        group by    YearBought

    )

    select      YearBought,
                n.sales,
                n.newSales,
                d.foregoneSales
    from        newSales n
    left join   droppedProds d on n.yearBought = d.droppedYear
    order by    YearBought;

Результат:

+------------+-------+----------+---------------+
| YearBought | sales | newSales | foregoneSales |
|------------|-------|----------|---------------|
|    2016    |  30   |    10    |               |  
|    2017    |  35   |          |      20       |  
|    2018    |  35   |     5    |               |  
|    2019    |  45   |          |               |  
|    2020    |  20   |          |      35       |
+------------+-------+----------+---------------+
person pwilcox    schedule 18.03.2020

arrow_upward
0
arrow_downward

Вы можете сначала перечислить даты, затем соединить их с исходной таблицей и выполнить условную агрегацию:

select
    y.yearbought,
    sum(case when t.yearbought = t.min_year then sales end) new_product_sales,
    sum(case when t.yearbought = t.max_year then sales end) dropped_product_sales
from (select distinct yearbought from #t) y
inner join #t on y.yearbought in (t.min_year, t.max_year + 1)
group by y.yearbought
person GMB    schedule 18.03.2020
comment
это работает для новых продуктов, но не работает для удаленных продуктов. Требуется сумма продаж для всех продуктов, когда продажи productid равны 0 в max_year, но имеют значение в max_year-1 - person Er In; 18.03.2020
comment
сумма (случай, когда t.yearbought=max_year и продажи = 0, затем продажи, где t.max_year = 1 заканчиваются) как drop_product_sales Я бы хотел, чтобы что-то подобное работало @GMB - person Er In; 18.03.2020

arrow_upward
0
arrow_downward

Я думаю, ваша проблема в том, что годы смещены.

with n as (
    select year_bought as y, sum(sales) as sales,
        sum(case when year_bought = min_year then sales end) as ns
    from T group by year_bought
), d as (
    select year_bought + 1 as y,
        sum(case when year_bought = max_year then sales end) as ds
    from T group by year_bought
)
select y, sales,
    ns as newSales, coalesce(dropped, 0) as foregoneSales
from n left outer join p on p.y = n.y;

или используйте lead()

select year_bought, sum(Sales) as Sales,
    sum(case when year_bought = min_year then sales end) as newSales,
    coalesce(lead(sum(case when year_bought = max_year then sales end))
        over (order by year_bought), 0) as foregoneSales
from T
group by year_bought;
person shawnt00    schedule 18.03.2020