SQL для поиска максимальной суммы данных в одной таблице с дополнительными столбцами

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

Данные выглядят так:

| order_id  | month  | cust_id  | state  | prod_id  | order_total  |
+-----------+--------+----------+--------+----------+--------------+
| 67212     | June   | 10001    | ca     | 909      | 13           |
| 69090     | June   | 10011    | fl     | 44       | 76           |
... etc ...

Мой запрос

SELECT `month`, `state`, SUM(order_total) AS sales 
FROM orders GROUP BY `month`, `state` 
ORDER BY sales;

|   month    | state  | sales  |
+------------+--------+--------+
| September  | wy     | 435    |
| January    | wy     | 631    |
... etc ...

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

Этот запрос

SELECT `state`, MAX(order_sum) as topmonth
FROM (SELECT `state`, SUM(order_total) order_sum FROM orders GROUP BY `month`,`state`) 
GROUP BY `state`;

| state  | topmonth  |
+--------+-----------+
| ca     | 119586    |
| ga     | 30140     |

возвращает правильное количество строк с правильными данными. НО я также хотел бы, чтобы запрос дал мне столбец месяца. Что бы я ни пытался с GROUP BY, я не могу найти способ ограничить результаты одной записью для каждого состояния. Я безуспешно пробовал PartitionBy, а также безуспешно пытался выполнить соединение.

TL; DR: один запрос дает мне правильные столбцы, но слишком много строк; другой запрос дает мне правильное количество строк (и правильные данные), но недостаточное количество столбцов.

Любые предложения по выполнению этой работы будут приняты с большой благодарностью.

Я использую Apache Drill, который, по-видимому, совместим с ANSI-SQL. Надеюсь, это не имеет большого значения - я предполагаю, что решение будет одинаковым для всех механизмов SQL.


sql sum max apache-drill
person Justin Watkins    schedule 23.03.2017    source источник

Ответы (2)


arrow_upward
1
arrow_downward

Это должно сработать

SELECT  t1.`month`, t1.`state`, t1.`sales`
FROM    (
            /* this one selects month, state and sales*/
            SELECT  `month`, `state`, SUM(order_total) AS sales 
            FROM    orders
            GROUP BY `month`, `state` 
        ) AS t1
JOIN    (
            /* this one selects the best value for each state */
            SELECT  `state`, MAX(sales) AS best_month
            FROM    (
                        SELECT  `month`, `state`, SUM(order_total) AS sales 
                        FROM    orders
                        GROUP BY `month`, `state` 
                    )
            GROUP BY `state`
        ) AS t2
ON      t1.`state` = t2.`state` AND
        t1.`sales` = t2.`best_month`

По сути, это комбинация двух написанных вами запросов.

person Stefano Zanini    schedule 23.03.2017
comment
Спасибо, Стефано, это работает и делает то, что мне нужно. У меня было предчувствие, что это будет соединение. - person Justin Watkins; 23.03.2017
comment
@JustinWatkins отметьте ответ Стефано как правильный ответ - person KindaTechy; 24.03.2017

arrow_upward
0
arrow_downward

Попробуй это:

 SELECT `month`, `state`, SUM(order_total) FROM orders WHERE `month` IN
  ( SELECT TOP 1 t.month  FROM ( SELECT  `month` AS month,  SUM(order_total) order_sum FROM orders GROUP BY `month`
ORDER BY  order_sum DESC) t) 
GROUP BY   `month`, state ;
person Abdullah Dibas    schedule 23.03.2017
comment
Спасибо, Абдулла. Похоже, что TOP не поддерживается в Apache Drill, и в этот момент этот запрос завершается ошибкой. Входит ли TOP в стандарт ANSI? - person Justin Watkins; 23.03.2017