如何旋转两列表格？

Question

这可能是一个新手问题——我还在学习。我在 PostgreSQL 9.6 上使用以下查询：

SELECT locales, count(locales) FROM (
    SELECT lower((regexp_matches(locale, '([a-z]{2,3}(-[a-z]{2,3})?)', 'i'))[1]) 
    AS locales FROM users) 
AS _ GROUP BY locales

我的查询返回以下动态行：

locales	count
en	10
fr	7
de	3
n extra locales (~300)...	n-count

我正在尝试旋转它以便区域设置值最终作为单行的列，如下所示：

en	fr	de	n extra locales (~300)...
10	7	3	n-count

我必须这样做才能很好地处理时间序列db/app

我尝试过使用 crosstab()，但所有示例都显示具有 3 列或更多列的更好定义的表。

我查看了使用 join 的示例，但我不知道如何动态地执行此操作。

Answer 1

基本查询

在 Postgres 10 或更高版本中，您可以使用更简单、更快的 regexp_match() 而不是 regexp_matches()。 （因为无论如何，您只获取每行的第一个匹配项。）但是不必费心，可以使用更简单的 substring() 来代替：

SELECT lower(substring(locale, '(?i)[a-z]{2,3}(?:-[a-z]{2,3})?')) AS locale
     , count(*)::int AS ct
FROM   users
WHERE  locale ~* '[a-z]{2,3}'  -- eliminate NULL, allow index support
GROUP  BY 1
ORDER  BY 2 DESC, 1

比原始基本查询更简单、更快。

关于 GROUP BY 和 ORDER BY 中的序数：

• 选择每个 GROUP BY 组中的第一行？

细微差别：regexp_matches()不匹配则返回无行，而substring() 返回null。我添加了一个 WHERE 子句来消除先验不匹配 - 并允许索引支持（如果适用），但我不希望索引在这里提供帮助。

请注意前缀 (?i)，这是所谓的 "嵌入选项" 使用不区分大小写的匹配。

添加了确定性 ORDER BY 子句。您需要它来实现简单的 crosstab()。

另外：对于“en_US”等区域设置，您可能需要在模式中使用 _ 而不是 -。

尽管您可能会尝试

，但 SQL 不允许在单个查询中使用动态结果列。您需要两次往返服务器。看;

• 如何在生成的表定义未知的情况下生成枢轴交叉联接？

您可以 使用动态生成的 crosstab() 查询。基础知识：

• PostgreSQL 交叉表查询

动态查询：

• PostgreSQL 将列转换为行？转置？

但是由于您生成单行纯整数值，我建议采用一种简单的方法：

SELECT 'SELECT ' || string_agg(ct || ' AS ' || quote_ident(locale), ', ')
FROM  (
   SELECT lower(substring(locale, '(?i)[a-z]{2,3}(?:-[a-z]{2,3})?')) AS locale
        , count(*)::int AS ct
   FROM   users
   WHERE  locale ~* '[a-z]{2,3}'
   GROUP  BY 1
   ORDER  BY 2 DESC, 1
   ) t

生成以下形式的查询：

SELECT 10 AS en, 7 AS fr, 3 AS de, 3 AS "de-at"

执行它以生成您的期望的结果。

在 psql 中，您可以将 \gexec 附加到生成的查询中，以将生成的 SQL 字符串立即反馈给服务器。请参阅：

• 我的函数返回一个字符串。如何执行？