如何將多個記錄分組為單個 .csv 字元串列？

我有一個關係，其中使用者有多條狗（多達 15 條），但每條狗都包含在表中的一行中，並且它們都有一個共同的 userId。

For example, Table `dogs`:
|   User    |    Dog Name     |  Age
|   ABCD    |      Fido       |  7
|   ABCD    |      Gooofy     |  9
|   ABCD    |      Toto       |  4
|   ABCD    |      Roger      |  12
|   ABCD    |      Barkley    |  3

我想做一些類似SELECT * FROM dogs GROUP BY User結果將輸出ABCDs 狗的 csv 的事情，如下所示：

ABCD, Fido, 7, Goofy, 9, Toto, 4, Roger, 12, Barkley, 3

我正在使用 BigQuery，但我認為知道如何在 PostgreSQL 中執行此操作甚至可能會有所幫助。所以也許我可以創建某種視圖或其他東西？

討論：

為了解決這個問題，我做了兩件事——第一件事是我按要求回答了這個問題，然後我以我認為更可靠的方式回答了這個問題。

這種“更好”的方法不是儲存age為欄位，因為根據定義，年齡是可變的 - 它每年都會增加，因此您應該通過將其與某物進行比較來相對於計算age日期的日期進行任何計算age這是不可變的，即出生日期/年份。

第 1 部分：按要求提問。

對於所問的問題，請參閱this fiddle for PostgreSQL code和this one for MySQL。

創建我們的表：

CREATE TABLE dog
(
 owner TEXT NOT NULL,
 name  TEXT NOT NULL,
 age   SMALLINT NOT NULL
);

填充它：

INSERT INTO dog VALUES
('abc', 'fido', 4),
('abc', 'tim',  6),
('abc', 'max',  9),
('abc', 'fred', 1),
('xyz', 'joe',  4),
('xyz', 'jim',  2);

然後執行這個 SQL：

SELECT 
 owner, 
 STRING_AGG(name || ' '  || age, ',' ORDER BY age) AS doggies
FROM dog
GROUP BY owner;

結果：

owner   doggies
abc     fred 1,fido 4,tim 6,max 9
xyz     jim 2,joe 4

Google 的 BigQuery 系統似乎使用STRING_AGG語法。

這兩個查詢都可以在我能找到的最舊的系統上進行測試——即 PostgreSQL 9.5 和 MySQL 5.5——這應該讓你開始！

第 2 部分：穩健方法。

現在，以更穩健的方式回答這個問題。dog 表現在有一個不可變的屬性 - 出生年份，我們總是可以從中計算出現在和未來任何時候的年齡。注意birth_year（永遠不會改變）而不是age.

我們創建第二個表：

CREATE TABLE dog_2
(
 owner      TEXT NOT NULL,
 name       TEXT NOT NULL,
 birth_year SMALLINT NOT NULL 

   CHECK (birth_year <= DATE_PART('YEAR', CURRENT_DATE) 
      AND birth_year >= DATE_PART('YEAR', CURRENT_DATE) - 30),

   -- Or the same thing written according to the SQL Standard.

   CHECK (birth_year <= EXTRACT('YEAR' FROM CURRENT_DATE) 
      AND birth_year >= EXTRACT('YEAR' FROM CURRENT_DATE) - 30)
);

30 數字不是從空中提取的——我是從這裡得到的！然後：

INSERT INTO dog_2 VALUES
('abc', 'fido', 2017),
('abc', 'tim',  2015),
('abc', 'max',  2012),
('abc', 'fred', 2020),
('xyz', 'joe',  2017),
('xyz', 'jim',  2019);

然後我們執行：

SELECT 
 owner,
 STRING_AGG
 (
   name || 
   ' '  || 
   (EXTRACT('YEAR' FROM CURRENT_DATE) - birth_year)::TEXT, ', ' ORDER BY birth_year DESC
 )
FROM
 dog_2
GROUP BY owner;

結果：

owner   string_agg
abc     fred 1, fido 4, tim 6, max 9
xyz     jim 2, joe 4

• 對於 PostgreSQL，請參閱相同的 9.5 fiddle - 兩者都適用！
• 對於 MySQL，有一個問題（quelle suprise）！它不會讓非確定性函式在CHECK約束中發揮作用——儘管 PostgreSQL 確實支持其中一些。在我看來，CURRENT_DATE() 是確定性的，因為對於給定的一組情況，它總是會返回相同的值 - 不像RAND()或UUID()……無論如何，YMMV。

然而，MySQL自 2019 年CHECK以來僅存在限制（距 SQL 標準強制要求僅 27 年！！！），因此只有限制，也許他們還有一些事情要做！這意味著小提琴也適用於版本 8 - 你可以在這裡找到它。

所以，我能為 MySQL 做的最好的事情如下：

CREATE TABLE dog_2
(
 owner      TEXT NOT NULL,
 name       TEXT NOT NULL,
 birth_year SMALLINT NOT NULL 

 CHECK (birth_year < 2021 AND birth_year > 1990)

--
-- Won't work!
--
--  CHECK (birth_year <= YEAR(CURRENT_DATE()) AND birth_year >= YEAR(CURRENT_DATE()) - 30)
--
);

請注意，這需要CHECK每年更新表定義中的約束——這可能比必須更新每條記錄要好一些？你甚至可以編寫一個EVENT?

並像上面一樣填充它，然後執行以下 SQL：

SELECT 
 owner, 
 GROUP_CONCAT(CONCAT
                (
                  name, 
                  ' ',  
                  YEAR(CURRENT_DATE()) - birth_year
                ) 
                ORDER BY birth_year 
                SEPARATOR ', '
             ) AS doggies
FROM dog_2
GROUP BY owner;

結果：

owner   doggies
abc     max 9, tim 6, fido 4, fred 1
xyz     joe 4, jim 2

如上所述，BigQuery 實現似乎使用 PostgreSQL 實現，因此您可能會走運！

引用自：https://dba.stackexchange.com/questions/295136