在SQL分区上进行连接吗? [英] Doing a concat over a partition in SQL?

问题描述

我有一些数据是这样排序的:

I have some data ordered like so:

date, uid, grouping
2018-01-01, 1, a
2018-01-02, 1, a
2018-01-03, 1, b
2018-02-01, 2, x
2018-02-05, 2, x
2018-02-01, 3, z
2018-03-01, 3, y
2018-03-02, 3, z

我想要一种最终形式，例如:

And I wanted a final form like:

uid, path
1, "a-a-b"
2, "x-x"
3, "z-y-z"

但运行类似

select
a.uid
,concat(grouping) over (partition by date, uid) as path
from temp1 a

似乎不太希望与SQL或Google BigQuery配合使用(这是我正在使用的特定环境).有没有足够简单的方法来将我所缺少的分组连接起来?我想有一种方法可以通过将一堆if-then语句作为自定义列，然后将结果串联在一起来强行执行，但是我敢肯定这会更加麻烦.有什么想法吗?

Doesn't seem to want to play well with SQL or Google BigQuery (which is the specific environment I'm working in). Is there an easy enough way to get the groupings concatenated that I'm missing? I imagine there's a way to brute force it by including a bunch of if-then statements as custom columns, then concatenating the result, but I'm sure that will be a lot messier. Any thoughts?

推荐答案

您正在寻找 string_agg():

select a.uid, string_agg(grouping, '-' order by date) as path
from temp1 a
group by a.uid;

这篇关于在SQL分区上进行连接吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！