LoginSignup

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

This article is a Private article. Only a writer and users who know the URL can access it.
Please change open range to public in publish setting if you want to share this article with other users.

More than 3 years have passed since last update.

@nyan_tech_24

SQL 第2版 ゼロからはじめるデータベース操作 8章

Posted at

#SQLで高度な処理を行う
##ウインドウ関数
別名OLAP関数でデータベースを使ってリアルタイムにデータ分析を行う処理。
市場分析、財務諸表作成、計画作成などの用途がある。

ウインドウ関数として使える関数
集約関数(SUM、AVG、COUNT、MAX、MIN)
RANK、DENSE_RANK、ROW_NUMBERなどのウインドウ専用関数

##RANK
レコードのランキングを算出する関数。
たとえば商品分類別に販売単価の安い順で並べたランキング表。

PARTITION BY はテーブルを複数の部分(ウインド）に小分けしたうえで、ウインド関数を使いたい場合のオプション。
ORDER BY はどの列をどんな順序で順位をつけるか指定する。

select shohin_mei, shohin_bunrui, hanbai_tanka, 
rank () over (partition by shohin_bunrui order by hanbai_tanka) as ranking 
from shohin;
   shohin_mei   | shohin_bunrui | hanbai_tanka | ranking 
----------------+---------------+--------------+---------
 Tシャツ        | 衣服          |         1000 |       1
 カッターシャツ | 衣服          |         4000 |       2
 ボールペン     | 事務用品      |          100 |       1
 穴あけパンチ   | 事務用品      |          500 |       2
 フォーク       | キッチン用品  |          500 |       1
 おろしがね     | キッチン用品  |          880 |       2
 包丁           | キッチン用品  |         3000 |       3
 圧力鍋         | キッチン用品  |         6800 |       4
(8 rows)

上記はPARTITION BYを商品分類に指定することで、商品グループ内でのランキングを出力している。

PARTITION BYを指定しないと以下のようになる。

 select shohin_mei, shohin_bunrui, hanbai_tanka, 
 rank () over ( order by hanbai_tanka) as ranking from shohin;
   shohin_mei   | shohin_bunrui | hanbai_tanka | ranking 
----------------+---------------+--------------+---------
 ボールペン     | 事務用品      |          100 |       1
 フォーク       | キッチン用品  |          500 |       2
 穴あけパンチ   | 事務用品      |          500 |       2
 おろしがね     | キッチン用品  |          880 |       4
 Tシャツ        | 衣服          |         1000 |       5
 包丁           | キッチン用品  |         3000 |       6
 カッターシャツ | 衣服          |         4000 |       7
 圧力鍋         | キッチン用品  |         6800 |       8
(8 rows)

###RANK関数
ランキングを算出。
同順位が複数レコード存在した場合
後続の順位が飛ぶ。
1位が3レコードある場合: 1位、1位、1位、4位...

###DENSE_RANK関数
ランキングを算出。
同順位が複数レコード存在した場合
降順の順位が飛ばない。
1位が3レコードある場合: 1位、1位、1位、2位...

###ROW_NUNBER関数
一意な連番を付与する。
1位が3レコードある場合: 1位、2位、3位、4位...

select shohin_mei, shohin_bunrui, hanbai_tanka,
rank () over (order by hanbai_tanka) as ranking,
dense_rank () over (order by hanbai_tanka) as dense_ranking,
row_number () over (order by hanbai_tanka) as row_num
from shohin;
  shohin_mei   | shohin_bunrui | hanbai_tanka | ranking | dense_ranking | row_num 
----------------+---------------+--------------+---------+---------------+---------
ボールペン     | 事務用品      |          100 |       1 |             1 |       1
フォーク       | キッチン用品  |          500 |       2 |             2 |       2
穴あけパンチ   | 事務用品      |          500 |       2 |             2 |       3
おろしがね     | キッチン用品  |          880 |       4 |             3 |       4
Tシャツ        | 衣服          |         1000 |       5 |             4 |       5
包丁           | キッチン用品  |         3000 |       6 |             5 |       6
カッターシャツ | 衣服          |         4000 |       7 |             6 |       7
圧力鍋         | キッチン用品  |         6800 |       8 |             7 |       8
(8 rows)

##集約関数とウインド専用関数
###SUM
SUMを使って累計総額を算出する

select shohin_id, shohin_mei, hanbai_tanka,
 sum(hanbai_tanka) over (order by shohin_id) as current_sum
 from shohin;
 shohin_id |   shohin_mei   | hanbai_tanka | current_sum 
-----------+----------------+--------------+-------------
 0001      | Tシャツ        |         1000 |        1000    -- 1000
 0002      | 穴あけパンチ   |          500 |        1500     -- 1000 + 1500
 0003      | カッターシャツ |         4000 |        5500       -- 1000 + 1500 + 5500 etc
 0004      | 包丁           |         3000 |        8500
 0005      | 圧力鍋         |         6800 |       15300
 0006      | フォーク       |          500 |       15800
 0007      | おろしがね     |          880 |       16680
 0008      | ボールペン     |          100 |       16780
(8 rows)

###AVG
平均はこちら

select shohin_id, shohin_mei, hanbai_tanka,
avg(hanbai_tanka) over (order by shohin_id) as current_avg
from shohin;
 shohin_id |   shohin_mei   | hanbai_tanka |      current_avg      
-----------+----------------+--------------+-----------------------
 0001      | Tシャツ        |         1000 | 1000.0000000000000000    -- (1000)/1
 0002      | 穴あけパンチ   |          500 |  750.0000000000000000     -- (1000 + 500)/2
 0003      | カッターシャツ |         4000 | 1833.3333333333333333       -- (1000 + 500 + 4000)/3 etc
 0004      | 包丁           |         3000 | 2125.0000000000000000
 0005      | 圧力鍋         |         6800 | 3060.0000000000000000
 0006      | フォーク       |          500 | 2633.3333333333333333
 0007      | おろしがね     |          880 | 2382.8571428571428571
 0008      | ボールペン     |          100 | 2097.5000000000000000
(8 rows)

###移動平均
こちらは直近3行の移動平均
2行前までというフレーム指定をしている。

 select shohin_id, shohin_mei, hanbai_tanka,
 avg(hanbai_tanka) over (order by shohin_id rows 2 preceding) as moving_avg
 from shohin;
 shohin_id |   shohin_mei   | hanbai_tanka |      moving_avg       
-----------+----------------+--------------+-----------------------
 0001      | Tシャツ        |         1000 | 1000.0000000000000000　  -- (1000)/1
 0002      | 穴あけパンチ   |          500 |  750.0000000000000000     -- (1000 + 500)/2
 0003      | カッターシャツ |         4000 | 1833.3333333333333333       -- (1000 + 500 + 4000)/3
 0004      | 包丁           |         3000 | 2500.0000000000000000   -- (500 + 4000 + 3000)/3
 0005      | 圧力鍋         |         6800 | 4600.0000000000000000    -- (4000 + 3000 + 6800)/3 etc
 0006      | フォーク       |          500 | 3433.3333333333333333
 0007      | おろしがね     |          880 | 2726.6666666666666667
 0008      | ボールペン     |          100 |  493.3333333333333333
(8 rows)

ROWSは「行」でPRECEDINGは「前」のというキーワードを使い「~行前まで」というフレーム指定をしている。
「ROWS 2 PRECEDING」は「２行前まで」というフレーム指定になり、対象レコードは以下。
・カレントレコード
・1行前のレコード
・2行前のレコード

FOLLOWINGというキーワードは「〜行後まで」というフレーム指定も可能。

select shohin_id, shohin_mei, hanbai_tanka,
avg(hanbai_tanka) over (order by shohin_id rows between 1 preceding and 1 following) as moving_avg
from shohin;
 shohin_id |   shohin_mei   | hanbai_tanka |      moving_avg       
-----------+----------------+--------------+-----------------------
 0001      | Tシャツ        |         1000 |  750.0000000000000000    -- (1000 + 500)/2
 0002      | 穴あけパンチ   |          500 | 1833.3333333333333333     -- (1000 + 500 + 4000)/3
 0003      | カッターシャツ |         4000 | 2500.0000000000000000       -- (500 + 4000 + 3000)/3
 0004      | 包丁           |         3000 | 4600.0000000000000000    -- (4000 + 3000 + 6800)/3 etc
 0005      | 圧力鍋         |         6800 | 3433.3333333333333333
 0006      | フォーク       |          500 | 2726.6666666666666667
 0007      | おろしがね     |          880 |  493.3333333333333333
 0008      | ボールペン     |          100 |  490.0000000000000000
(8 rows)

1行前のレコード
カレントレコード
１行後のレコード
という3行の指定。

RANK () OVER (ORDER BY hanbai_tanka)
のORDER BY句 は決してRANKの順序を指定している訳ではない。
なのでRANKING順にしたい場合は以下のように記述する。

select shohin_mei, shohin_bunrui, hanbai_tanka,
rank () over (order by hanbai_tanka) as ranking
from shohin
order by ranking;

###GROUPING演算子
GROUP BY句だけだと合計と小計を同時に求めることはできない。

select shohin_bunrui, sum(hanbai_tanka)
from shohin
group by shohin_bunrui;
shohin_bunrui |  sum  
---------------+-------
キッチン用品  | 11180
衣服          |  5000
事務用品      |   600
(3 rows)

GROUP BY句だけだと上記のようになる。

なので合計行も一緒に求めたい場合、
合計行と商品分類ごとの集約結果を別個にもとめ、UNION ALLをつかって「ドッキング」させる。

select '合計' as shohin_bunrui, sum(hanbai_tanka)
from shohin
union all
select shohin_bunrui, sum(hanbai_tanka)
from shohin
group by shohin_bunrui;
 shohin_bunrui |  sum  
---------------+-------
 合計          | 16780
 キッチン用品  | 11180
 衣服          |  5000
 事務用品      |   600
(4 rows)

上記のようにできるが、select文を2回実行して、その結果をくっつけるので、
冗長で、DBMS内での処理コストも高くつく。

これを同時にやるのがGROPPING演算子。
GROUPING演算子には
ROLLUP
CUBE
GROUPING SETS
という3種類がある。

###ROLLUP
合計値を一緒に求めることができるのがROLLUP

select shohin_bunrui, sum(hanbai_tanka) as sum_tanka 
 from shohin
 group by rollup(shohin_bunrui);
 shohin_bunrui | sum_tanka 
---------------+-----------
               |     16780
 キッチン用品  |     11180
 衣服          |      5000
 事務用品      |       600
(4 rows)

ここで登録日をGROUP BY句で追加してみる。
ますはROLLUPなし

select shohin_bunrui, torokubi, sum(hanbai_tanka) as sum_tanka
from shohin
group by shohin_bunrui, torokubi;
 shohin_bunrui |  torokubi  | sum_tanka 
---------------+------------+-----------
 衣服          |            |      4000
 キッチン用品  | 2009-01-15 |      6800
 衣服          | 2009-09-20 |      1000
 キッチン用品  | 2008-04-28 |       880
 事務用品      | 2009-11-11 |       100
 事務用品      | 2009-09-11 |       500
 キッチン用品  | 2009-09-20 |      3500
(7 rows)

ROLLUPありで実行する

｀｀｀sql
select shohin_bunrui, torokubi, sum(hanbai_tanka) as sum_tanka
from shohin
group by rollup(shohin_bunrui, torokubi)
order by shohin_bunrui;
shohin_bunrui | torokubi | sum_tanka
---------------+------------+-----------
衣服 | 2009-09-20 | 1000
衣服 | | 4000
衣服 | | 5000 -- 小計
事務用品 | 2009-09-11 | 500
事務用品 | 2009-11-11 | 100
事務用品 | | 600 -- 小計
キッチン用品 | 2009-01-15 | 6800
キッチン用品 | 2009-04-28 | 880
キッチン用品 | 2009-09-20 | 3500
キッチン用品 | | 11180 -- 小計
| | 16780 --　総計
(11 rows)


ROLLUPなしの場合は衣服の合計しか出力されていない。
ROLLUPありの場合は全ての分類の合計と、分類ごとの合計を表示できる。

ただし、登録日に入ってるNULLと合計が出力されているNULLどっちがどっちかわかりにくい。
なのでGROUPING関数を使う。
 GROUPING関数は超集合行で生じたNULLなら1、それ以外は0を返す。

```sql
 select grouping (shohin_bunrui) as shohin_bunrui,
 grouping(torokubi) as torokubi, sum(hanbai_tanka) as sum_tanka
 from shohin
 group by rollup(shohin_bunrui, torokubi);
 shohin_bunrui | torokubi | sum_tanka 
---------------+----------+-----------
             1 |        1 |     16780
             0 |        0 |      4000
             0 |        0 |      6800
             0 |        0 |      1000
             0 |        0 |       880
             0 |        0 |       100
             0 |        0 |       500
             0 |        0 |      3500
             0 |        1 |     11180
             0 |        1 |      5000
             0 |        1 |       600
(11 rows)

上記のように1の部分は合計が返っているので、その1に文字列を与えると以下のようになる。

select case when grouping(shohin_bunrui) = 1
            then '商品分類 合計'
            else shohin_bunrui end as shohin_bunrui,
       case when grouping(torokubi) = 1
            then '登録日 合計'
            else cast(torokubi as varchar(16)) end as torokubi,
sum(hanbai_tanka) as sum_tanka
from shohin
group by rollup(shohin_bunrui, torokubi);
 shohin_bunrui |  torokubi   | sum_tanka 
---------------+-------------+-----------
 商品分類 合計 | 登録日 合計 |     16780
 衣服          |             |      4000
 キッチン用品  | 2009-01-15  |      6800
 衣服          | 2009-09-20  |      1000
 キッチン用品  | 2008-04-28  |       880
 事務用品      | 2009-11-11  |       100
 事務用品      | 2009-09-11  |       500
 キッチン用品  | 2009-09-20  |      3500
 キッチン用品  | 登録日 合計 |     11180
 衣服          | 登録日 合計 |      5000
 事務用品      | 登録日 合計 |       600
(11 rows)

###CUBE
データで積み木を作る
GROUP BY句に与えられた集約キーの「すべての可能な組み合わせ」を一つの結果に含める機能。

GROUPING SETS
これはROLLUPやCUBEで求めた結果の一部のレコードだけを求めれば良い場合に使う。

select case when grouping(shohin_bunrui) = 1
            then '商品分類 合計'
            else shohin_bunrui end as shohin_bunrui,
       case when grouping(torokubi) = 1
            then '登録日 合計'
            else cast(torokubi as varchar(16)) end as torokubi,
sum(hanbai_tanka) as sum_tanka
from shohin
group by grouping sets (shohin_bunrui, torokubi);
 shohin_bunrui |  torokubi   | sum_tanka 
---------------+-------------+-----------
 キッチン用品  | 登録日 合計 |     11180
 衣服          | 登録日 合計 |      5000
 事務用品      | 登録日 合計 |       600
 商品分類 合計 |             |      4000
 商品分類 合計 | 2009-11-11  |       100
 商品分類 合計 | 2009-09-20  |      4500
 商品分類 合計 | 2009-09-11  |       500
 商品分類 合計 | 2009-01-15  |      6800
 商品分類 合計 | 2008-04-28  |       880
(9 rows)
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?