SQLでの並べ替えについて

Question

前提・実現したいこと

GCP Bigquery内部の下記の形式のテーブル(table1)を保持しています。
id列には重複があるidデータ
data1, data2には整数値が入っています。

id	data1	data2
a	int64	int64
a	int64	int64
b	int64	int64
b	int64	int64
b	int64	int64
c	int64	int64
e	int64	int64

この形式のデータに対し、下記のようにid出現頻度順に並べ替えを行いたいのですが、どのようにしたらよいでしょうか。

id	data1	data2
b	int64	int64
b	int64	int64
b	int64	int64
a	int64	int64
a	int64	int64
c	int64	int64
e	int64	int64

試したこと

各idの出現頻度数を算出するところまではできています。

select
id, count(id)
from
table1
group by
id
order by
count(id)

補足情報（FW/ツールのバージョンなど）

ここにより詳細な情報を記載してください。

Answer 1

試していませんが、以下でどうでしょう

select * from table1 t1
order by (select count(*) from table1 where id=t1.id) desc

Comments

[yambejp]

たしかに絞り込むほうが効率的ですね

[yutakudo12]

ありがとうございます！
実際に動くことも確認できました！

[sazi]

一応Postgresで動作確認していましたが、問題なかったと云う事で良かったです。

Answer 2

select * from table1
order by count(*) over(partition by id) desc;

Comments

[mysql_help]

OracleとかPostgreSQLは、標準SQL通り、OrderBy句でWindow関数を使えますが、BigQueryで可能かは分かりませんでした。

Answer 3

例えばこんな感じ
(書式はMySQLのものなので、順次適当なSQLに読み替えてください)

create table tbl(id varchar(10),data1 int,data2 int);
insert into tbl values('a',1,10),('a',2,20),('b',5,30),('b',3,40),('b',4,50),('c',3,20),('e',2,10);

select * from tbl as t1 order by (select count(*) from tbl group by id having id=t1.id ) desc,data1 asc,data2 asc

Comments

[yutakudo12]

ありがとうございます！
動作も確認できました！