Cloud Spanner でクエリの結果に連番を振ろうと思ったら ROW_NUMBER
関数がなかったので代わりの方法を探しました。(似たような関数も見つからなかった)
cloud.google.com
サンプルデータ
ChatGPT に作ってもらったサンプルデータ。
CREATE TABLE Singers (
SingerId STRING(36) NOT NULL,
FirstName STRING(1024),
LastName STRING(1024),
BirthDate DATE
) PRIMARY KEY (SingerId);
INSERT INTO Singers (SingerId, FirstName, LastName, BirthDate) VALUES
('f47ac10b-58cc-4372-a567-0e02b2c3d479', 'John', 'Lennon', '1940-10-09'),
('7e57d004-2b97-0e7a-8b1e-4946d34c0ae4', 'Paul', 'McCartney', '1942-06-18'),
('2403d2c7-2277-4faf-908a-0798f91e0908', 'George', 'Harrison', '1943-02-25'),
('3f8c7d18-a6cc-4855-955c-3838387d09d8', 'Ringo', 'Starr', '1940-07-07'),
('e17d09a8-4c21-4eff-be52-2634681c3d7d', 'Mick', 'Jagger', '1943-07-26'),
('624615d9-5c3d-4aee-a9f8-213c5f577ab9', 'Keith', 'Richards', '1943-12-18'),
('0ae8db3a-a4e2-43df-9b64-b0d8e7c4f3db', 'Robert', 'Plant', '1948-08-20'),
('e1f095d7-1ce3-40c0-a6aa-743278862d4c', 'Jimmy', 'Page', '1944-01-09'),
('52c9e469-2886-491f-a6ea-e841e1625b04', 'Roger', 'Daltrey', '1944-03-01'),
('a8b3c0c9-3f90-4bdf-8f04-d078f3a4f9a5', 'Pete', 'Townshend', '1945-05-19');
連番を出力する
例えば、誕生日で並び替えた結果に連番を出力する場合。
SELECT
(SELECT count(*) + 1 FROM Singers Singers2 WHERE Singers.BirthDate > Singers2.BirthDate),
*
FROM Singers ORDER BY BirthDate;
結果:
| |SingerId |FirstName|LastName |BirthDate |
|---|------------------------------------|---------|---------|----------|
|1 |3f8c7d18-a6cc-4855-955c-3838387d09d8|Ringo |Starr |1940-07-07|
|2 |f47ac10b-58cc-4372-a567-0e02b2c3d479|John |Lennon |1940-10-09|
|3 |7e57d004-2b97-0e7a-8b1e-4946d34c0ae4|Paul |McCartney|1942-06-18|
|4 |2403d2c7-2277-4faf-908a-0798f91e0908|George |Harrison |1943-02-25|
|5 |e17d09a8-4c21-4eff-be52-2634681c3d7d|Mick |Jagger |1943-07-26|
|6 |624615d9-5c3d-4aee-a9f8-213c5f577ab9|Keith |Richards |1943-12-18|
|7 |e1f095d7-1ce3-40c0-a6aa-743278862d4c|Jimmy |Page |1944-01-09|
|8 |52c9e469-2886-491f-a6ea-e841e1625b04|Roger |Daltrey |1944-03-01|
|9 |a8b3c0c9-3f90-4bdf-8f04-d078f3a4f9a5|Pete |Townshend|1945-05-19|
|10 |0ae8db3a-a4e2-43df-9b64-b0d8e7c4f3db|Robert |Plant |1948-08-20|
ついでに、出力した連番を使って任意の ID を付けたい。
SELECT
FORMAT('BirthDate-%02d', (SELECT count(*) + 1 FROM Singers Singers2 WHERE Singers.BirthDate > Singers2.BirthDate)),
*
FROM Singers ORDER BY BirthDate;
結果:
| |SingerId |FirstName|LastName |BirthDate |
|------------|------------------------------------|---------|---------|----------|
|BirthDate-01|3f8c7d18-a6cc-4855-955c-3838387d09d8|Ringo |Starr |1940-07-07|
|BirthDate-02|f47ac10b-58cc-4372-a567-0e02b2c3d479|John |Lennon |1940-10-09|
|BirthDate-03|7e57d004-2b97-0e7a-8b1e-4946d34c0ae4|Paul |McCartney|1942-06-18|
|BirthDate-04|2403d2c7-2277-4faf-908a-0798f91e0908|George |Harrison |1943-02-25|
|BirthDate-05|e17d09a8-4c21-4eff-be52-2634681c3d7d|Mick |Jagger |1943-07-26|
|BirthDate-06|624615d9-5c3d-4aee-a9f8-213c5f577ab9|Keith |Richards |1943-12-18|
|BirthDate-07|e1f095d7-1ce3-40c0-a6aa-743278862d4c|Jimmy |Page |1944-01-09|
|BirthDate-08|52c9e469-2886-491f-a6ea-e841e1625b04|Roger |Daltrey |1944-03-01|
|BirthDate-09|a8b3c0c9-3f90-4bdf-8f04-d078f3a4f9a5|Pete |Townshend|1945-05-19|
|BirthDate-10|0ae8db3a-a4e2-43df-9b64-b0d8e7c4f3db|Robert |Plant |1948-08-20|
現場からは以上です。