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

SQL-按唯一列集分组

postgresql sql-server sql

Troy · 技术社区 · 8 年前

从问题出发” SQL - Turn relationship IDs into a delimited list “,我想进一步按独特的站点集对结果进行分组。例如,在该问题中使用相同的数据:

站点15&16一起共享a、b、c的一个排列。站点18、19、20共享另一个排列。站点17使用两种排列。

我想找到一个查询,其结果与链接的问题类似,但在site_id中没有任何重叠:

我不确定只使用SQL是否可行,我可能只需要构建一些东西来处理代码中的数据,但我正在祈祷。。。

我使用的是SQL Server,但知道如何使用postgres也会很好。

更新:
有人建议 Simulating group_concat MySQL function in Microsoft SQL Server 2005? 可能是重复的。从我对group_concat的(有限的)理解来看,它可能会复制 question referenced above ,但不是这个问题。这个问题在site_id中不需要重叠。

2 回复 | 直到 7 年前

Tom H 8 年前

我不知道这是否是最有效的方法,但想出它确实很有趣

;WITH CTE_unique_groups AS (SELECT DISTINCT a, b, c FROM dbo.My_Table),
CTE_numbered_groups AS (SELECT a, b, c, ROW_NUMBER() OVER(ORDER BY a, b, c) AS row_num FROM CTE_unique_groups),
CTE_exponented_groups AS (SELECT a, b, c, POWER(2, row_num) AS group_value FROM CTE_numbered_groups),
CTE_unique_sets AS (
    SELECT
        MT.site_id,
        SUM(grps.group_value) AS total_group_value
    FROM
        CTE_exponented_groups AS grps
    INNER JOIN dbo.My_Table MT ON MT.a = grps.a AND MT.b = grps.b AND MT.c = grps.c
    GROUP BY
        MT.site_id
),
CTE_grouped_sites AS
(SELECT total_group_value, STUFF((SELECT ', ' + CAST(site_id AS VARCHAR(10)) FROM CTE_unique_sets t2 WHERE t2.total_group_value = t1.total_group_value FOR XML PATH('')), 1, 2, '') AS site_ids
FROM CTE_unique_sets t1)
SELECT DISTINCT
    x.a, x.b, x.c, gs.site_ids
FROM
    CTE_grouped_sites gs
INNER JOIN CTE_exponented_groups x ON
    gs.total_group_value & x.group_value = x.group_value

它的要点是,首先你必须识别每一组独特的组。完成后,您可以按位使用 & 操作员将站点ID分解为它们所属的组。

Hambone 8 年前

如果您对一个数组(它以逗号分隔的字符串的形式显示)很满意,那么在PostgreSQL中有一种可能的方法。

with perms as (
  select
    f.a, f.b, f.c, f.site_id,
    count(1) over (partition by f.site_id) as cnt
  from
    your_table f
)
select
  p1.a, p1.b, p1.c,
  array (select p2.site_id
         from perms p2
         where
           p1.a = p2.a and
           p1.b = p2.b and
           p1.c = p2.c and
           p1.cnt = p2.cnt)
from perms p1
group by
  p1.a, p1.b, p1.c, p1.cnt

结果如下:

bar    baz    blah    {20,18,19}
foo    bar    baz     {17}
foo    bar    baz     {16,15}
bar    baz    blah    {17}