Cloud Spanner で ROW_NUMBER の代わりに連番を出力したい

SQL

Cloud Spanner でクエリの結果に連番を振ろうと思ったら ROW_NUMBER 関数がなかったので代わりの方法を探しました。(似たような関数も見つからなかった)

cloud.google.com

サンプルデータ

ChatGPT に作ってもらったサンプルデータ。

-- DDL
CREATE TABLE Singers (
    SingerId   STRING(36) NOT NULL,
    FirstName  STRING(1024),
    LastName   STRING(1024),
    BirthDate  DATE
) PRIMARY KEY (SingerId);

-- DML
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|

現場からは以上です。