LoginSignup
@ryo1217intern

Are you sure you want to delete the question?

If your question is resolved, you may close it.

Leaving a resolved question undeleted may help others!

We hope you find it useful!

SQLのSTRING_AGG(について)

Q&A

SQLBigQuery

SQL初心者です。環境はBigQueryです。
id別にcontract_numberが同じのものに限って文字列を重ねたいです。
contract_start_dateが同じ日数のものに限りcontract_numberが一緒になっている使用です。
以下が例です。

[id,contract_started_date,subject,subsubject,contract_number]
[12,1day,math,math1,1]
[12,3day,math,math2,2]
[12,3day,english,english2,2]
[12,4day,math,math3,3]
[15,7day,math,math1,1]
[15,7day,math,math2,1]
→
[12,1day,math,math1,1]
[12,3day,math+english,math2+english2,2]
[12,4day,math,math3,3]
[15,7day,math,math1+math2,1]

分かりづらく申し訳ありません。

0 likes

1Answer

mrbonjin
@mrbonjin

こんな感じですかね。

SELECT
  id,
  contract_started_date,
  STRING_AGG(DISTINCT subject, '+'),
  STRING_AGG(DISTINCT subsubject, '+'),
  contract_number,
FROM UNNEST(
  ARRAY<STRUCT<
    id INT,
    contract_started_date STRING,
    subject STRING,
    subsubject STRING,
    contract_number INT>>[
      (12,'1day','math','math1',1),
      (12,'3day','math','math2',2),
      (12,'3day','english','english2',2),
      (12,'4day','math','math3',3),
      (15,'7day','math','math1',1),
      (15,'7day','math','math2',1)
    ])
GROUP BY id, contract_started_date, contract_number;
0Like

Your answer might help someone💌

LoginSign Up