Блог

Главная - Вопросы по программированию - Как выбрать лучшие элементы, упорядоченные по сумме за каждый месяц в диапазоне времени?

Как выбрать лучшие элементы, упорядоченные по сумме за каждый месяц в диапазоне времени?

#sql #soql

Вопрос:

На данный момент мой запрос выглядит так:

 SELECT TOP 1000 
    Yr, Mnth, Product, SUM(Price * Amount)
FROM
    (SELECT 
         Random_product_name AS Product, Random_price AS Price, 
         Random_quantity AS Amount,
         Random_order_dt AS Order_date, 
         YEAR(Random_order_dt) AS Yr, MONTH(Random_order_dt) AS Mnth
     FROM 
         Random_table_1 a
     JOIN 
         Random_table_2 b ON a.Random_ID = b.Random_ID
     WHERE 
         a.Random_product_name LIKE 'XX_%' 
         AND a.Random_country = 'XX'
         AND a.Random_price != 0 
         AND b.Random_order_dt BETWEEN 'Jan 01 2019 12:00AM' AND 'Jul 01 2021 12:00AM') x
GROUP BY 
    Yr, Mnth, Product
ORDER BY 
    Yr, CONVERT(INT, Mnth), CONVERT(INT, SUM(Price*Amount)) DESC

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

Год Mnth Продукт СУММА
2019 1 A 100 000
2019 1 B 90 000
2019 1 C 80 000
2019 1 D 70 000
2019 1 E 60 000
2019 1 F 50 000
2019 1 G 40 000
2019 2 B 120 000
2019 2 A 110 000
2019 2 D 90 000
2019 2 C 80 000
2019 2 E 60 000
2019 2 G 30 000
2019 2 F 20 000

И так далее вплоть до июля 2021 года. Сумма продуктов меняется каждый месяц, и чего я хотел бы добиться, так это отображать только верхние 3 из них за каждый месяц, но в одной таблице, вот так:

Год Mnth Продукт СУММА
2019 1 A 100 000
2019 1 B 90 000
2019 1 C 80 000
2019 2 B 120 000
2019 2 A 110 000
2019 2 D 90 000

И так далее. Опять же, вплоть до июля 2021 года.

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

Ответ №1:

Вы можете использовать row_number с предложением partition_by в своем операторе select, а затем использовать результат в своем предложении where.

Чтобы добавить в начало -выберите :

  row_number() over(partition by Yr, Mnth order by SUM(Price * Amount) desc) as rownum

Затем добавьте select поверх следующего предложения where:

 WHERE rownum <= 3

Ответ №2:

Я думаю, что ROW_NUMBER может вам помочь

Я не мог протестировать, и я знаю, что это не очень хорошее решение, но я думаю, что это общая идея:

     SELECT * FROM  
        (SELECT 
            Yr, Mnth, Product, sold
            ROW_NUMBER() OVER (
                    PARTITION BY Yr, Mnth, Product 
                    ORDER BY sold DESC
            ) item_rank
        FROM
        (
            SELECT TOP 1000 
                Yr, Mnth, Product, SUM(Price * Amount) as sold
            FROM
                (SELECT 
                    Random_product_name AS Product, Random_price AS Price, 
                    Random_quantity AS Amount,
                    Random_order_dt AS Order_date, 
                    YEAR(Random_order_dt) AS Yr, MONTH(Random_order_dt) AS Mnth
                FROM 
                    Random_table_1 a
                JOIN 
                    Random_table_2 b ON a.Random_ID = b.Random_ID
                WHERE 
                    a.Random_product_name LIKE 'XX_%' 
                    AND a.Random_country = 'XX'
                    AND a.Random_price != 0 
                    AND b.Random_order_dt BETWEEN 'Jan 01 2019 12:00AM' AND 'Jul 01 2021 12:00AM') x
            GROUP BY 
                Yr, Mnth, Product
            ORDER BY 
                Yr, CONVERT(INT, Mnth), CONVERT(INT, SUM(Price*Amount)) DESC
        )
    )
    WHERE item_rank <= 3
Метки: ,