交叉表转置查询请求 [英] Crosstab transpose query request

问题描述

使用Postgres 9.3.4，我有此表:

Using Postgres 9.3.4, I've got this table:

create table tbl1(country_code text, metric1 int, metric2 int, metric3 int);
insert into tbl1 values('us', 10, 20, 30);
insert into tbl1 values('uk', 11, 21, 31);
insert into tbl1 values('fr', 12, 22, 32);

我需要一个交叉表查询才能将其转换为此:

I need a crosstab query to convert it to this:

create table tbl1(metric text, us int, uk int, fr int);
insert into tbl1 values('metric1', 10, 11, 12);
insert into tbl1 values('metric2', 20, 21, 22);
insert into tbl1 values('metric3', 30, 31, 32);

作为额外的奖励，我喜欢汇总:

As an added bonus, I'd love a rollup:

create table tbl1(metric text, total int, us int, uk int, fr int);
insert into tbl1 values('metric1', 33, 10, 11, 12);
insert into tbl1 values('metric2', 63, 20, 21, 22);
insert into tbl1 values('metric3', 93, 30, 31, 32);

我已经看完了交叉表规范，已经用case语句写了它，但是它很生气而且很漫长，所以能熟练使用交叉表的人可以快速查询一下以便我继续前进吗?

I'm done staring at the crosstab spec, I have it written with case statements but it's mad unruly and long, so can someone who's fluent in crosstab please whip up a quick query so I can move on?

推荐答案

特别的困难是您的数据尚未准备好进行交叉制表.您需要的数据格式为行名，类别，值.您可以通过UNION查询获得该信息:

The special difficulty is that your data is not ready for cross tabulation. You need data in the form row_name, category, value. You can get that with a UNION query:

SELECT 'metric1' AS metric, country_code, metric1 FROM tbl1
UNION ALL
SELECT 'metric2' AS metric, country_code, metric2 FROM tbl1
UNION ALL
SELECT 'metric3' AS metric, country_code, metric3 FROM tbl1
ORDER  BY 1, 2 DESC;

但是智能的LATERAL查询只需要进行一次表扫描，并且速度更快:

But a smart LATERAL query only needs a single table scan and will be faster:

SELECT x.metric, t.country_code, x.val
FROM   tbl1 t
     , LATERAL (VALUES
         ('metric1', metric1)
       , ('metric2', metric2)
       , ('metric3', metric3)
       ) x(metric, val)
ORDER  BY 1, 2 DESC;

相关:

• 什么是和PostgreSQL中的子查询之间有什么区别?
• 在多个列上选择DISTINCT

• What is the difference between LATERAL and a subquery in PostgreSQL?
• SELECT DISTINCT on multiple columns

使用带有1个参数的crosstab()的简单形式，并将此查询作为输入:

Using the simple form of crosstab() with 1 parameter with this query as input:

SELECT * FROM crosstab(
 $$SELECT x.metric, t.country_code, x.val
   FROM   tbl1 t
        , LATERAL (VALUES
            ('metric1', metric1)
          , ('metric2', metric2)
          , ('metric3', metric3)
          ) x(metric, val)
   ORDER  BY 1, 2 DESC$$
   )
AS ct (metric text, us int, uk int, fr int);

以字母降序列出国家名称(如您的演示中所示). 这也假定所有指标均定义为NOT NULL.

List country names in alphabetically descending order (like in your demo). This also assumes all metrics are defined NOT NULL.

如果不是一个或两个都不是，请改用2参数形式:

If one or both are not the case, use the 2-parameter form instead:

• PostgreSQL交叉表查询

即每个指标总计:

SELECT * FROM crosstab(
 $$SELECT x.metric, t.country_code, x.val
   FROM  (
      TABLE tbl1
      UNION ALL
      SELECT 'zzz_total', sum(metric1)::int, sum(metric2)::int, sum(metric3)::int  -- etc.
      FROM tbl1
      ) t
        , LATERAL (VALUES
            ('metric1', metric1)
          , ('metric2', metric2)
          , ('metric3', metric3)
          ) x(metric, val)
   ORDER  BY 1, 2 DESC$$
   )
AS ct (metric text, total int, us int, uk int, fr int);

'zzz_total'是任意标签，必须按字母顺序最后排序(或者您需要crosstab()的2参数形式).

'zzz_total' is an arbitrary label, that must sort last alphabetically (or you need the 2-parameter form of crosstab()).

如果您有很多指标列，则可能要动态构建查询字符串.相关:

If you have lots of metrics columns, you might want to build the query string dynamically. Related:

• 如何在不列出各列的情况下对每一列执行相同的聚合?
• 在PL/pgSQL中动态执行查询

• How to perform the same aggregation on every column, without listing the columns?
• Executing queries dynamically in PL/pgSQL

还要注意，即将发布的Postgres 9.5(当前为beta)引入了专用的

Also note that the upcoming Postgres 9.5 (currently beta) introduces a dedicated SQL clause for ROLLUP.
Related:

• 在具有多个自我联接的大型表执行缓慢

这篇关于交叉表转置查询请求的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！