Блог

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

• Таблица articles (такие поля, как as name, manufacturer_id и т.д.).
• Таблица sales .
  • FK к статьям, называемым article_id
  • Целое число, называемое amount
  • date Поле
  • Вызываемое поле type . Мы можем предположить, что это строка, и она может иметь 3 известных значения — 'morning' , 'evening' и 'night'

Я хочу сгенерировать сводный отчет о продажах с указанием даты начала и даты окончания:

   -------------- --------------- -------------- ------------- 
 | article_name | morning_sales | evening_sales| night_sales |
  -------------- --------------- -------------- ------------- 
 | article 1    |             0 |           12 |           2 |
 | article 2    |            80 |            3 |           0 |
...            ...             ...            ...           ...
 | article n    |            37 |           12 |           1 |
  -------------- --------------- -------------- ------------- 
  

Я хотел бы сделать это как можно эффективнее. До сих пор мне удавалось сгенерировать запрос, который даст мне один тип продажи (утренний, вечерний или ночной), но я не могу сделать это для нескольких типов одновременно. Возможно ли это вообще?

Это то, что у меня есть на данный момент; это даст мне название статьи и утренние продажи всех статей за данный период — другими словами, первые две колонки отчета:

 SELECT articles.name as article_name,
       SUM(sales.amount) as morning_sales,
FROM "sales"
INNER JOIN articles ON articles.id = sales.articles_id
WHERE ( sales.date >= '2011-05-09'
    AND sales.end_date <= '2011-05-16'
    AND sales.type = 'morning'
)
GROUP BY sales.article_id
  

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

• Если мне нужно выполнить 1 запрос для каждого типа продажи, как мне «смешать и сопоставить» разные списки статей, которые я получу?
• Есть ли лучший способ сделать это (возможно, с помощью каких-либо подзапросов)?

Аналогичным образом, я могу создать запрос, который выдает мне все утренние, вечерние и ночные продажи, сгруппированные по типу. Я предполагаю, что моя проблема в том, что мне нужно выполнить два ГРУППОВЫХ запроса, чтобы получить этот отчет. Я не знаю, как это сделать, если это вообще возможно.

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

К счастью, вам не нужно выполнять несколько запросов с вашим форматом базы данных. Это должно сработать для вас:

 SELECT
  articles.name AS article_name
  SUM(IF(sales.type = 'morning', sales.amount, 0.0)) AS morning_sales,
  SUM(IF(sales.type = 'evening', sales.amount, 0.0)) AS evening_sales,
  SUM(IF(sales.type = 'night', sales.amount, 0.0)) AS night_sales
FROM sales
  JOIN articles ON sales.article_id = articles.id
WHERE
  sales.date >= "2011-01-01 00:00:00"
  AND sales.date < "2011-02-01 00:00:00"
GROUP BY sales.article_id
  

И если существуют другие типы, вам пришлось бы добавить туда больше столбцов ИЛИ просто суммировать другие типы, добавив это в предложение SELECT:

 SUM(
  IF(sales.type IS NULL OR sales.type NOT IN ('morning', 'evening', 'night'), 
    sales.amount, 0.0
  )
) AS other_sales
  

Вышеуказанное совместимо с MySQL. Чтобы использовать его в Postgres, я думаю, вам пришлось бы изменить IF выражения на CASE expressions, которые должны выглядеть следующим образом (непроверенные):

 SELECT
  articles.name AS article_name,
  SUM(CASE WHEN sales.type = 'morning' THEN sales.amount ELSE 0.0 END) AS morning_sales,
  SUM(CASE WHEN sales.type = 'evening' THEN sales.amount ELSE 0.0 END) AS evening_sales,
  SUM(CASE WHEN sales.type = 'night' THEN sales.amount ELSE 0.0 END) AS night_sales
FROM sales
  JOIN articles ON sales.article_id = articles.id
WHERE
  sales.date >= "2011-01-01 00:00:00"
  AND sales.date < "2011-02-01 00:00:00"
GROUP BY sales.article_id
  

Два варианта.

Вариант 1. Одиночное объединение с вычисляемыми столбцами для объединения

 select article_name  = a.article_name ,
       morning_sales = sum( case when sales.type = 'morning' then sales.amount end ) ,
       evening_sales = sum( case when sales.type = 'evening' then sales.amount end ) ,
       night_sales   = sum( case when sales.type = 'night'   then sales.amount end ) ,
       other_sales   = sum( case when sales.type in ( 'morning','evening','night') then null else sales.amount end ) ,
       total_sales   = sum( sales.amount )
from articles a
join sales    s on s.articles_id = a.articles_id
where sales.date between @dtFrom and @dtThru
group by a.article_name
  

Вариант 2. множественные объединения слева

 select article_name = a.article_name ,
       morning_sales = sum( morning.amount ) ,
       evening_sales = sum( evening.amount ) ,
       night_sales   = sum( night.amount   ) ,
       other_sales   = sum( other.amount   ) ,
       total_sales   = sum( total.amount   )
from      articles a
left join sales    morning on morning.articles_id = a.articles_id
                          and morning.type        = 'morning'
                          and morning.date between @dtFrom and @dtThru
left join sales    evening on evening.articles_id = a.articles_id
                          and evening.type        = 'evening'
                          and evening.date between @dtFrom and @dtThru
left join sales    night   on night.articles_id   = a.articles_id
                          and night.type          = 'evening'
                          and night.date between @dtFrom and @dtThru
left join sales    other   on other.articles_id = a.articles_id
                          and (    other.type is null
                                OR other.type not in ('morning','evening','night')
                              )
                          and other.date between @dtFrom and @dtThru
left join sales    total   on total.articles_id = a.articles_id
                          and total.date between @dtFrom and @dtThru
group by a.article_name