sql - 如果不知道可能值，如何计算列中不同值出现的次数？

Question

给定项目表

                  id                  |       lccnumber        |               libraryid
--------------------------------------+------------------------+--------------------------------------
 d6f7c1ba-a237-465e-94ed-f37e91bc64bd | PR6056.I4588           | 5d78803e-ca04-4b4a-aeae-2c63b924518b
 1714f71f-b845-444b-a79e-a577487a6f7d | RC60 .A5               | 5d78803e-ca04-4b4a-aeae-2c63b924518b
 1b6d3338-186e-4e35-9e75-1b886b0da53e | PR6056.I4588           | 5d78803e-ca04-4b4a-aeae-2c63b924518b
 4428a37c-8bae-4f0d-865d-970d83d5ad55 | PR6056.I4588           | c2549bb4-19c7-4fcc-8b52-39e612fb7dbe
 7212ba6a-8dcf-45a1-be9a-ffaa847c4423 | TK5105.88815 .A58 2004 | 5d78803e-ca04-4b4a-aeae-2c63b924518b
 100d10bf-2f06-4aa0-be15-0b95b2d9f9e3 | TK5105.88815 .A58 2004 | c2549bb4-19c7-4fcc-8b52-39e612fb7dbe

是否有产生结果集的SQL查询

       lccnumber        | 5d78803e-ca04-4b4a-aeae-2c63b924518b | c2549bb4-19c7-4fcc-8b52-39e612fb7dbe
------------------------+--------------------------------------+--------------------------------------
 PR6056.I4588           |                   2                  |                    1
 RC60 .A5               |                   1                  |                    0
 TK5105.88815 .A58 2004 |                   1                  |                    1

如果可能的 libraryid 是提前知道的，那么我可以做类似的事情

SELECT lccNumber,
       SUM(CASE WHEN libraryId = 5d78803e-ca04-4b4a-aeae-2c63b924518b THEN 1 ELSE 0) AS 5d78803e-ca04-4b4a-aeae-2c63b924518b,
       SUM(CASE WHEN libraryId = c2549bb4-19c7-4fcc-8b52-39e612fb7dbe THEN 1 ELSE 0) AS c2549bb4-19c7-4fcc-8b52-39e612fb7dbe
FROM items
GROUP BY lccNumber;

但我正在寻找一种解决方案，以防它们不提前知道。一种可能有效的方法是首先查询可能的 libraryId，然后以编程方式构造一个 SELECT 子句来解释所有这些值，但我想知道是否有是实现它的更简单或更有效的方法。

Answer 1

您可以将值放在单独的行中:

SELECT lccNumber, libraryId, COUNT(*)
FROM items
GROUP BY lccNumber, libraryId;

然后在应用层重新编排。您可以将这些组合成记录，并为每个 lccNumber 聚合成一个数组:

SELECT lccNumber, ARRAY_AGG( (libraryId, cnt) )
FROM (SELECT lccNumber, libraryId, COUNT(*) as cnt
      FROM items
      GROUP BY lccNumber, libraryId
     ) l
GROUP BY lccNumber