sql - 如何有效地将不同的值计入SQL中的不同行？

Question

问题:

假设有一个简单(但很大)的表 foods

id   name 
--   -----------  
01   ginger beer
02   white wine
03   red wine
04   ginger wine

我想计算有多少条目具有特定的硬编码模式，比如包含单词“ginger”(LIKE '%ginger%')或“wine”(LIKE '%wine %')，或其中的任何其他内容，并将这些数字沿着注释写入行中。我正在寻找的结果如下

comment           total 
---------------   -----  
contains ginger   2
for wine lovers   3

方案一(格式好但效率低):

可以使用 UNION ALL 并构造以下内容

SELECT * FROM
(
  (
    SELECT
      'contains ginger' AS comment,
      sum((name LIKE '%ginger%')::INT) AS total
    FROM foods
  )
  UNION ALL
  (
    SELECT
      'for wine lovers' AS comment,
      sum((name LIKE '%wine%')::INT) AS total
    FROM foods
  )
)

显然，它的工作原理类似于简单地执行多个查询，然后将它们缝合在一起。这是非常低效的。

解决方案 2(高效但格式不佳):

与以前的解决方案相比，以下方案快了数倍

SELECT
  sum((name LIKE '%ginger%')::INT) AS contains_ginger,
  sum((name LIKE '%wine%')::INT) AS for_wine_lovers
FROM foods

结果是

contains_ginger   for_wine_lovers 
---------------   ---------------  
2                 3

所以绝对有可能更快地获得相同的信息，但格式错误......

讨论:

最好的整体方法是什么？我应该怎么做才能以有效的方式和更可取的格式获得我想要的结果？还是真的不可能？

顺便说一句，我正在为 Redshift(基于 PostgreSQL)写这篇文章。

谢谢。

Answer 1

在两个查询中都使用了 LIKE 运算符。或者，我们可以使用 Position 来查找名称中硬编码单词的位置。如果名称中有硬编码词，则返回一个大于 0 的数字。

SELECT 
       unnest(array['ginger', 'wine']) AS comments,
       unnest(array[ginger, wine]) AS count
FROM(
     (SELECT sum(contains_ginger) ginger , sum(contains_wine) wine
        FROM
             (SELECT CASE WHEN Position('ginger' in name)>0 
                          THEN 1 
                           END contains_ginger,
                     CASE WHEN Position('wine' in name) > 0 
                          THEN 1
                           END contains_wine
                 FROM foods) t) t1