Ruby on rails - 执行两个 GROUP BY 的 SQL 查询

Question

我在获取需要生成的报告的 SQL 时遇到问题。我有（相当于）以下设置：

• 表articles（如名称、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 个请求，我如何"混合和匹配"我将获得的不同文章列表？
• 有没有更好的方法来做到这一点（也许使用某种子查询）？

同样，我

能够构建一个查询，为我提供所有早间、晚间和夜间销售，按类型分组。我想我的问题是我需要做两个 GROUP BY 才能获得这份报告。我不知道该怎么做，如果可能的话。

我正在使用PostgreSQL作为我的数据库，但我想尽可能保持标准。如果有帮助，那么使用它的应用程序就是Rails应用程序。

Answer 1

幸运的是，您不需要对数据库格式执行多个查询。这应该适合您：

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表达式，它们应如下所示（未经测试）：

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

Answer 2

两个选项。

选项 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