代码之家 › 专栏 › 技术社区 › t56k

Postgres:字符串聚合和串联

postgresql sql

t56k · 技术社区 · 7 年前

如果我有这样的查询:

SELECT 
    u.client_code, 
    max(d.created_at) as last_document_created_at, 
    u.brand_id, 
    t.name as template_name, 
    count(d)
FROM users u
    INNER JOIN documents d ON u.id = d.user_id
    INNER JOIN templates t ON t.id = d.template_id
GROUP BY 1, 3, 4

返回如下信息:

client_code last_document_created_at    brand_id template_name  count
---------------------------------------------------------------------
client1     2017-12-06 10:03:47 +1100   39       newsletter_r   1
client1     2017-12-05 15:23:24 +1100   39       Other media    5
client2     2017-12-21 17:07:11 +1100   39       newsletter_r   4
client3     2018-01-11 12:10:43 +1100   39       newsletter_r   2
client3     2017-12-06 11:45:21 +1100   39       Other media    1

连接 template_name 和 count 字段,以便每个用户(以 u.client_code )在一条线上吗?我知道我可以打电话 string_agg 类似这样的列:

...
string_agg(distinct t.name, ', ') as template_name, 
...

但这当然破坏了各自的计算:

newsletter_r, Other media   6

使现代化

我可以这样做:

string_agg(concat_ws(': ', t.name::text, count(d)::text), ', ') as template_count

但这给了我一个错误:

aggregate function calls cannot be nested LINE 5: string_agg(concat_ws(': ', t.name::text, count(d)::text)... ^ : SELECT u.client_code,

2 回复 | 直到 7 年前

ravioli 7 年前

不确定要如何设置连接字段的格式,但是否尝试将原始查询放入子查询并应用 string_agg 对吗?类似这样:

SELECT client_code, STRING_AGG(template_name || template_count, ',') 
FROM (
    SELECT 
        u.client_code, 
        MAX(d.created_at) AS last_document_created_at, 
        u.brand_id, 
        t.name AS template_name, 
        COUNT(d) AS template_count
    FROM users u
    INNER JOIN documents d ON u.id = d.user_id
    INNER JOIN templates t ON t.id = d.template_id
    GROUP BY 1, 3, 4
) src
GROUP BY client_code

我还没有测试它,所以您可能有一些语法错误。如果有效,请告诉我。

Gordon Linoff 7 年前

我想你想要这样的东西:

SELECT u.client_code, 
       max(d.created_at) as last_document_created_at, 
       u.brand_id, 
       string_agg(t.name, ',') as template_name, 
       count(distinct d.id)
FROM users u INNER JOIN
     documents d
     ON u.id = d.user_id INNER JOIN
     templates t
     ON t.id = d.template_id
GROUP BY 1, 3;

推荐文章

Ishaan Adarsh · 未知的META规范,无法验证。[规范v1.0.1]

1 年前

tggtsed · PostgreSQL从平均值中排除值

1 年前

Dawid · 为什么我不能在子查询中使用表别名?

2 年前

LAZ · ORDER BY id DESC LIMIT 100返回第一行而不是最后一行

2 年前

Mohammed Shaheer · 查找列中的数据是否满足sql中的数据类型条件

2 年前

AKang123. · Postgres:重复行,但更新json中的字段

2 年前

CraZyCoDer · 在PostgreSQL中锁定潜在事务

2 年前

ranebec · 计数时如何返回0而不是null?

2 年前

Ramón J Romero y Vigil · 将没有时区列的postgres时间戳转换为日期

2 年前

Flo · 分组依据中的SQL大小写

2 年前