WebLog

順列や組み合わせをsqlで表現する

2023/03/28 12:57

準備

ディレクトリ構成

1.
2├── compose.yaml
3└── postgres
4 └── init
5 └── 01_initialize.sql

DB とテーブルを作成

compose.yaml
1services:
2 db:
3 image: postgres
4 container_name: postgres
5 ports:
6 - 5432:5432
7 volumes:
8 - ./postgres/init:/docker-entrypoint-initdb.d
9 environment:
10 POSTGRES_USER: postgres
11 POSTGRES_PASSWORD: password
./postgres/init/01_initialize.sql
1-- DB作成
2CREATE DATABASE test_db;
3
4-- 作成したDBへ切り替え
5\c test_db
6
7CREATE TABLE public.products (
8 name VARCHAR(10) NOT NULL,
9 price INTEGER NOT NULL
10);
11
12INSERT INTO public.products VALUES('apple', 50);
13INSERT INTO public.products VALUES('orange', 100);
14INSERT INTO public.products VALUES('grape', 50);
15INSERT INTO public.products VALUES('strawberry', 100);
16INSERT INTO public.products VALUES('lemon', 80);
17INSERT INTO public.products VALUES('banana', 100);

テスト

1docker compose up
2psql -h localhost -p 5432 -U postgres -d test_db
1test_db=# select * from products;
2 name | price
3------------+-------
4 apple | 50
5 orange | 100
6 grape | 50
7 strawberry | 100
8 lemon | 80
9 banana | 100
10(6 rows)

重複順列

1select p1.name as name_1, p2.name as name_2
2from products p1 cross join products p2;
1 name_1 | name_2
2------------+------------
3 apple | apple
4 apple | orange
5 apple | grape
6 apple | strawberry
7 apple | lemon
8 apple | banana
9 orange | apple
10 orange | orange
11 orange | grape
12 orange | strawberry
13 orange | lemon
14 orange | banana
15 grape | apple
16 grape | orange
17 grape | grape
18 grape | strawberry
19 grape | lemon
20 grape | banana
21 strawberry | apple
22 strawberry | orange
23 strawberry | grape
24 strawberry | strawberry
25 strawberry | lemon
26 strawberry | banana
27 lemon | apple
28 lemon | orange
29 lemon | grape
30 lemon | strawberry
31 lemon | lemon
32 lemon | banana
33 banana | apple
34 banana | orange
35 banana | grape
36 banana | strawberry
37 banana | lemon
38 banana | banana
39(36 rows)

順列

1select p1.name as name_1, p2.name as name_2
2from products p1 inner join products p2 on p1.name <> p2.name;
1 name_1 | name_2
2------------+------------
3 apple | orange
4 apple | grape
5 apple | strawberry
6 apple | lemon
7 apple | banana
8 orange | apple
9 orange | grape
10 orange | strawberry
11 orange | lemon
12 orange | banana
13 grape | apple
14 grape | orange
15 grape | strawberry
16 grape | lemon
17 grape | banana
18 strawberry | apple
19 strawberry | orange
20 strawberry | grape
21 strawberry | lemon
22 strawberry | banana
23 lemon | apple
24 lemon | orange
25 lemon | grape
26 lemon | strawberry
27 lemon | banana
28 banana | apple
29 banana | orange
30 banana | grape
31 banana | strawberry
32 banana | lemon
33(30 rows)

組み合わせ

1select p1.name as name_1, p2.name as name_2
2from products p1 inner join products p2 on p1.name > p2.name;
1 name_1 | name_2
2------------+--------
3 orange | apple
4 orange | grape
5 orange | lemon
6 orange | banana
7 grape | apple
8 grape | banana
9 strawberry | apple
10 strawberry | orange
11 strawberry | grape
12 strawberry | lemon
13 strawberry | banana
14 lemon | apple
15 lemon | grape
16 lemon | banana
17 banana | apple
18(15 rows)

3つの組み合わせ

1select p1.name as name_1, p2.name as name_2, p3.name as name_3
2from products p1 inner join products p2 on p1.name > p2.name
3inner join products p3 on p2.name > p3.name;
1 name_1 | name_2 | name_3
2------------+--------+--------
3 orange | grape | apple
4 orange | grape | banana
5 orange | lemon | apple
6 orange | lemon | grape
7 orange | lemon | banana
8 orange | banana | apple
9 grape | banana | apple
10 strawberry | orange | apple
11 strawberry | orange | grape
12 strawberry | orange | lemon
13 strawberry | orange | banana
14 strawberry | grape | apple
15 strawberry | grape | banana
16 strawberry | lemon | apple
17 strawberry | lemon | grape
18 strawberry | lemon | banana
19 strawberry | banana | apple
20 lemon | grape | apple
21 lemon | grape | banana
22 lemon | banana | apple
23(20 rows)

重複組み合わせ

1select p1.name as name_1, p2.name as name_2
2from products p1 inner join products p2 on p1.name >= p2.name;
1 name_1 | name_2
2------------+------------
3 apple | apple
4 orange | apple
5 orange | orange
6 orange | grape
7 orange | lemon
8 orange | banana
9 grape | apple
10 grape | grape
11 grape | banana
12 strawberry | apple
13 strawberry | orange
14 strawberry | grape
15 strawberry | strawberry
16 strawberry | lemon
17 strawberry | banana
18 lemon | apple
19 lemon | grape
20 lemon | lemon
21 lemon | banana
22 banana | apple
23 banana | banana
24(21 rows)

参考

達人に学ぶSQL徹底指南書 第2版 初級者で終わりたくないあなたへ | ミック |本 | 通販 | Amazon

達人に学ぶSQL徹底指南書 第2版 初級者で終わりたくないあなたへ | ミック |本 | 通販 | Amazon

Amazonでミックの達人に学ぶSQL徹底指南書 第2版 初級者で終わりたくないあなたへ。アマゾンならポイント還元本が多数。ミック作品ほか、お急ぎ便対象商品は当日お届けも可能。また達人に学ぶSQL徹底指南書 第2版 初級者で終わりたくないあなたへもアマゾン配送商品なら通常配送無料。

最新の投稿