SQL のお勉強

—

by

admin
in

　SQL のお勉強です。たまに使わないと色々忘れてしまうので・・・。

　想定問題として２年分の日雨量データ rainfall.csv

2023-01-01,0.0
2023-01-02,0.0
2023-01-03,0.0
    :
2024-12-29,0.0
2024-12-30,0.0
2024-12-31,4.5

から各年の最大日雨量、最大２日雨量、最大３日雨量を抽出するクエリを考えてみた。
　次のスクリプト rainfall.sql は試行錯誤を経て完成したもの。サブクエリ内で窓関数 (over) を使って２日雨量、３日雨量を計算するのがポイント。何時ものことながら出来てみれば論理が明確だ。

create table rainfall(date text, value real);

.mode csv
.import rainfall.csv rainfall

.header on

select
    T.year as year,
    max(T.d1) as max_d1,
    max(T.d2) as max_d2,
    max(T.d3) as max_d3
from (  -- サブクエリ
    select
        strftime('%Y', date) as year,
        value as d1, -- 日雨量
        sum(value) over (
            rows between 1 preceding and current row
        ) as d2,     -- ２日雨量
        sum(value) over (
            rows between 2 preceding and current row
        ) as d3      -- ３日雨量
    from rainfall
) as T
group by year;

　で、コマンドプロンプトで

sqlite3 :memory: < rainfall.sql

とすると、

year,max_d1,max_d2,max_d3
2023,40.0,57.0,58.0
2024,31.0,37.0,38.0

が出力される。これまた使い方を忘れがちな pandas より可成りお手軽だ。