ColaboratoryでSQL:PostgreSQL

この記事は[PostgreSQL Advent Calendar 2022](https://qiita.com/advent-calendar/2022/postgresql)の六日目です。

以前の記事は @fujii_masao さんの記事になります。

https://qiita.com/fujii_masao/items/d9a1a62414551255c78b

# はじめに

https://oss-db.jp/

で使用されているPostgreSQL

SQL(PostgreSQL)を勉強するために、[psql-basics](https://www.crunchydata.com/developers/playground)とか、オンラインでできるところもあります。が、少し画面が狭いです。

今回は使いなれているGoogle Colaboratoryでやってみようと思います。

# コード

```python:postgres_sql.ipynb
%%capture

# https://www.postgresql.org/download/linux/debian/

!sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'
!wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
!sudo apt-get update
!sudo apt-get -y install postgresql-15
!sudo service postgresql start

# Setup a password `postgres` for username `postgres`
!sudo -u postgres psql -U postgres -c "ALTER USER postgres PASSWORD 'postgres';"

# Setup a database with name `tutorial` to be used
!sudo -u postgres psql -U postgres -c 'DROP DATABASE IF EXISTS tutorial;'
!sudo -u postgres psql -U postgres -c 'CREATE DATABASE tutorial;'

# set connection
%env DATABASE_URL=postgresql://postgres:postgres@localhost:5432/tutorial
#To load the sql extention to start using %%sql
%load_ext sql
%config SqlMagic.autopandas = True
```

`!sudo apt-get -y install postgresql` をそのままやると、colaboratoryはバージョン１２をダウンロードします。
せっかくなので、最新版をダウンロードしたいので、[ホームページ](https://www.postgresql.org/download/linux/debian/)のやり方をそのまま踏襲しています。

パスワードの設定等については、google colaboratoryのチュートリアルから持ってきました。

# データの入力

```python:
# https://github.com/simongeek/PandasDA

!wget -P /tmp/ https://raw.githubusercontent.com/simongeek/PandasDA/master/weather.csv

import pandas as pd
df = pd.read_csv('/tmp/weather.csv')

# Make variables some friendlier names for users
old_names = ['Max TemperatureF', 'Min TemperatureF', 'Mean TemperatureF', 'Max Dew PointF', 'MeanDew PointF',
             'Min DewpointF', 'Max Humidity',
             ' Mean Humidity', ' Min Humidity', ' Max Sea Level PressureIn', ' Mean Sea Level PressureIn',
             ' Min Sea Level PressureIn', ' Max VisibilityMiles', ' Mean VisibilityMiles',
             ' Min VisibilityMiles', ' Max Wind SpeedMPH', ' Mean Wind SpeedMPH', ' Max Gust SpeedMPH', 'PrecipitationIn',
             ' CloudCover', ' WindDirDegrees', ' Events']
new_names = ['max_Temp', 'min_Temp', 'mean_Temp', 'max_Dew', 'mean_Dew', 'min_Dew', 'max_Hum', 'mean_Hum', 'min_Hum', 'max_Press',
             'min_Press', 'mean_Press', 'max_Vis', 'mean_Vis',
             'min_Vis', 'max_Wind', 'mean_Wind', 'max_Gust', 'preIn', 'cloud', 'Wind_Dir', 'events']
df.rename(columns=dict(zip(old_names, new_names)), inplace=True)

df.columns = [c.lower() for c in df.columns] # PostgreSQL doesn't like capitals or spaces

df['prein']=df['prein'].mask(df['prein']=='T',0.01) # Digitize T of PrecipitationIn

from sqlalchemy.types import Integer,Text,Numeric,SmallInteger,Date

table_dict={'zip': Text(),
 'cloud': SmallInteger(),
 'wind_dir': SmallInteger(),
 'index': SmallInteger(),
 'max_temp': SmallInteger(),
 'mean_temp': SmallInteger(),
 'min_temp': SmallInteger(),
 'max_dew': SmallInteger(),
 'mean_dew': SmallInteger(),
 'min_dew': SmallInteger(),
 'max_hum': SmallInteger(),
 'mean_hum': SmallInteger(),
 'min_hum': SmallInteger(),
 'max_press': Numeric(3,1),
 'min_press': Numeric(3,1),
 'mean_press': Numeric(3,1),
 'max_vis': Numeric(3,1),
 'mean_vis': Numeric(3,1),
 'min_vis': Numeric(3,1),
 'max_wind': SmallInteger(),
 'mean_wind': SmallInteger(),
 'max_gust': Numeric(3,1),
 'pdt': Date(),
 'prein': Numeric(4,3),
 'events': Text()}

from sqlalchemy import create_engine

engine = create_engine('postgresql://postgres:postgres@localhost:5432/tutorial')

df.to_sql("weather", engine,dtype=table_dict, index=False)
```

読み込み方については[Can I automatically create a table in PostgreSQL from a csv file with headers?](https://stackoverflow.com/questions/21018256/can-i-automatically-create-a-table-in-postgresql-from-a-csv-file-with-headers)
から。

データは[Pandas + Seaborn tutorial on Weather data for San Francisco Bay Area in California](https://github.com/simongeek/PandasDA)から。

単純に`to_sql()`で読み込むとデータ型がすごくおかしくなってしまったので、修正してみました。



## データ型の修正の仕方

```python:
# https://stackoverflow.com/questions/109325/postgresql-describe-table

%%sql
select column_name, data_type, character_maximum_length, column_default, is_nullable
from INFORMATION_SCHEMA.COLUMNS where table_name = 'weather';
```

このクエリーで`psql -c "\d weather"`と同じ内容がでるので、`data_type`の値をみて、
[The Type Hierarchy - SQLAlchemy
](https://docs.sqlalchemy.org/en/14/core/type_basics.html)
[pandas.DataFrame.to_sql](https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.to_sql.html)
の二つをみて、修正しました。

# テスト

```sql:
%%sql
select * from weather limit 10;
```

うまくいきました。

## psqlについて

`!sudo -u postgres psql -U postgres -d tutorial`
で一応psqlのプロンプトは出るけど、入力値は見えないし、簡易的にしか使えなかったです。

なんかいい方法があるのかな :thinking: 


# まとめ

とりあえずは、準備よしです。

あとは、いろいろな本をみながらやっていきましょう。

## おまけ

https://oss-db.jp/ossdbtext

の演習で使用する郵便番号データ。

pdfをコピペするとずれるので。

```python:zip.ipynb
!wget --quiet  http://www.post.japanpost.jp/zipcode/dl/kogaki/zip/ken_all.zip

!unzip ken_all.zip
```

```sql:
%%sql
DROP TABLE IF EXISTS zip;

CREATE TABLE zip (
lgcode char(5), 
oldzip char(5),
newzip char(7),
prefkana text,
citykana text,
areakana text,
pref text,
city text,
area text,
largearea integer,
koaza integer,
choume integer,
smallarea integer,
change integer,
reason integer
);

-- https://www.postgresql.jp/document/9.3/html/sql-copy.html
COPY zip FROM '/content/KEN_ALL.CSV' (FORMAT csv, ENCODING 'sjis');
```

```sql:確認
%%sql

select * from zip
where newzip between '1000000' and '2000000' limit 5;
```

綺麗にできました。

## おまけ　その２

せっかくAdvent Calenderに乗っけるのでもう少しデータが欲しくなった。
気象庁のデータがあったので、やってみたら・・・・`orz`

### データと加工と取り込み

```python:colaboratoryで引き続き
# データの入手
#　昭和26年（1951年）以降の梅雨入りと梅雨明け（確定値）
# https://www.data.jma.go.jp/cpd/baiu/index.html

!wget "https://www.data.jma.go.jp/cpd/baiu/tsuyu_iriake.csv"

# csvはsjisだし、ヘッダーは２重になっているので、そのまま取り込めないのでpandasにお任せ
import pandas as pd

df = pd.read_csv('tsuyu_iriake.csv', encoding='sjis',header=[0,1])

# https://stackoverflow.com/questions/45670981/reading-csv-with-sparsely-labeled-column-headers-using-pandas

df.columns = pd.MultiIndex.from_arrays(
    [df.columns.get_level_values(0).to_series().mask(lambda x: x.str.startswith('Unnamed')).ffill(), df.columns.get_level_values(1)]
)

# 上のやり方で列の名前を整える
# そのあと、データベースに取り込む時のデータ型を整えるために平均値の行を削除する。
df = df[1:]

# すごい汚くなってしまったけど、地方毎ループさせて、コラムを２つ作っている。
ddf=pd.DataFrame()
for i in df.columns.get_level_values(level=0).drop_duplicates()[1:]:
    _df = df[i]
    col_name = i + "_入り"
    ddf[col_name]= df.iloc[:,0].astype('str')+"-"+_df.iloc[:,0].astype('str')+"-"+_df.iloc[:,1].astype('str')
    col_name = i + "_開け"
    ddf[col_name]= df.iloc[:,0].astype('str')+"-"+_df.iloc[:,2].astype('str')+"-"+_df.iloc[:,3].astype('str')

# 取得できていない月には-9999が入っているので、全部削除して時間に変換
sql_data = ddf.replace("^.*9999.*$","",regex=True).apply(lambda x: pd.to_datetime(x,infer_datetime_format=True))

# 用意しているデータベースに入れ込む
from sqlalchemy import create_engine
from sqlalchemy.types import Date, DateTime

engine = create_engine('postgresql://postgres:postgres@localhost:5432/tutorial')


sql_data.to_sql("梅雨", engine, index=False, if_exists='replace', dtype={k: Date() for k in sql_data.columns})

```

```sql:確認
%%sql

select * from 梅雨 limit 5;
```

なんとかなった。

```sql:確認
%%sql
select extract(YEAR FROM 沖縄地方_入り) as 年, 沖縄地方_開け - 沖縄地方_入り as 期間
from 梅雨 ORDER BY 期間 desc
limit 5;
```

|	|年	|期間
|:-:|:-:|:-:|
0|	1962|	60
1|	1982|	60
2|	1975|	59
3|	2021|	59
4|	1995|	58

沖縄は梅雨が長いですね。

でも、実際どこが一番ながかったんだろう :thinking: 

### 梅雨の期間を確認してみる

```python:
%config SqlMagic.autopandas = False
```

```sql:
%%sql
SELECT concat('SELECT ''',
B.column_name,
''' AS 列名, extract(YEAR FROM ',
column_name,
') AS 年,',
column_name,
' AS 日付 FROM 梅雨 UNION ALL') FROM 
(select column_name
from INFORMATION_SCHEMA.COLUMNS
where table_name = '梅雨') AS B;
```
で抽出文を作成してコピペと少し修正

```sql:
%%sql

CREATE TABLE 梅雨_t
AS
SELECT '沖縄地方_入り' AS 列名, extract(YEAR FROM 沖縄地方_入り) AS 年,沖縄地方_入り AS 日付 FROM 梅雨 UNION ALL
SELECT '沖縄地方_開け' AS 列名, extract(YEAR FROM 沖縄地方_開け) AS 年,沖縄地方_開け AS 日付 FROM 梅雨 UNION ALL
SELECT '奄美地方_入り' AS 列名, extract(YEAR FROM 奄美地方_入り) AS 年,奄美地方_入り AS 日付 FROM 梅雨 UNION ALL
SELECT '奄美地方_開け' AS 列名, extract(YEAR FROM 奄美地方_開け) AS 年,奄美地方_開け AS 日付 FROM 梅雨 UNION ALL
SELECT '九州南部_入り' AS 列名, extract(YEAR FROM 九州南部_入り) AS 年,九州南部_入り AS 日付 FROM 梅雨 UNION ALL
SELECT '九州南部_開け' AS 列名, extract(YEAR FROM 九州南部_開け) AS 年,九州南部_開け AS 日付 FROM 梅雨 UNION ALL
SELECT '九州北部地方（山口県を含む）_入り' AS 列名, extract(YEAR FROM 九州北部地方（山口県を含む）_入り) AS 年,九州北部地方（山口県を含む）_入り AS 日付 FROM 梅雨 UNION ALL
SELECT '九州北部地方（山口県を含む）_開け' AS 列名, extract(YEAR FROM 九州北部地方（山口県を含む）_開け) AS 年,九州北部地方（山口県を含む）_開け AS 日付 FROM 梅雨 UNION ALL
SELECT '四国地方_入り' AS 列名, extract(YEAR FROM 四国地方_入り) AS 年,四国地方_入り AS 日付 FROM 梅雨 UNION ALL
SELECT '四国地方_開け' AS 列名, extract(YEAR FROM 四国地方_開け) AS 年,四国地方_開け AS 日付 FROM 梅雨 UNION ALL
SELECT '中国地方_入り' AS 列名, extract(YEAR FROM 中国地方_入り) AS 年,中国地方_入り AS 日付 FROM 梅雨 UNION ALL
SELECT '中国地方_開け' AS 列名, extract(YEAR FROM 中国地方_開け) AS 年,中国地方_開け AS 日付 FROM 梅雨 UNION ALL
SELECT '近畿地方_入り' AS 列名, extract(YEAR FROM 近畿地方_入り) AS 年,近畿地方_入り AS 日付 FROM 梅雨 UNION ALL
SELECT '近畿地方_開け' AS 列名, extract(YEAR FROM 近畿地方_開け) AS 年,近畿地方_開け AS 日付 FROM 梅雨 UNION ALL
SELECT '東海地方_入り' AS 列名, extract(YEAR FROM 東海地方_入り) AS 年,東海地方_入り AS 日付 FROM 梅雨 UNION ALL
SELECT '東海地方_開け' AS 列名, extract(YEAR FROM 東海地方_開け) AS 年,東海地方_開け AS 日付 FROM 梅雨 UNION ALL
SELECT '関東甲信地方_入り' AS 列名, extract(YEAR FROM 関東甲信地方_入り) AS 年,関東甲信地方_入り AS 日付 FROM 梅雨 UNION ALL
SELECT '関東甲信地方_開け' AS 列名, extract(YEAR FROM 関東甲信地方_開け) AS 年,関東甲信地方_開け AS 日付 FROM 梅雨 UNION ALL
SELECT '北陸地方_入り' AS 列名, extract(YEAR FROM 北陸地方_入り) AS 年,北陸地方_入り AS 日付 FROM 梅雨 UNION ALL
SELECT '北陸地方_開け' AS 列名, extract(YEAR FROM 北陸地方_開け) AS 年,北陸地方_開け AS 日付 FROM 梅雨 UNION ALL
SELECT '東北南部_入り' AS 列名, extract(YEAR FROM 東北南部_入り) AS 年,東北南部_入り AS 日付 FROM 梅雨 UNION ALL
SELECT '東北南部_開け' AS 列名, extract(YEAR FROM 東北南部_開け) AS 年,東北南部_開け AS 日付 FROM 梅雨 UNION ALL
SELECT '東北北部_入り' AS 列名, extract(YEAR FROM 東北北部_入り) AS 年,東北北部_入り AS 日付 FROM 梅雨 UNION ALL
SELECT '東北北部_開け' AS 列名, extract(YEAR FROM 東北北部_開け) AS 年,東北北部_開け AS 日付 FROM 梅雨;
```

そして
```sql:
%%sql

SELECT A.地方, A.年, B.日付 開け日付, A.日付 入り日付, (B.日付-A.日付) 日数
FROM
(select split_part("列名",'_',1)  地方, split_part("列名",'_',2)  入り開け,年, 日付
from 梅雨_t
WHERE split_part("列名",'_',2)='入り') A
JOIN
(select split_part("列名",'_',1)  地方, split_part("列名",'_',2)  入り開け,年, 日付
from 梅雨_t
WHERE split_part("列名",'_',2)='開け') B
ON A.地方=B.地方 AND A.年=B.年
ORDER BY 日数 desc;
```

地方|	年|	開け日付|	入り日付|	日数|
|:-:|:-:|:-:|:-:|--:|
|九州南部|	1954|	1954-08-01	|1954-05-13	|80
|九州北部地方（山口県を含む）|	1954|	1954-08-01	|1954-05-13	|80
|関東甲信地方|	1963|	1963-07-24	|1963-05-06	|79
|九州南部|	1956|	1956-07-13	|1956-05-01	|73
|奄美地方|	2010|	2010-07-15	|2010-05-06	|70

昔は九州の梅雨が長かったみたいです。

```sql:内部結合を使わないでやってみた。
%%sql

SELECT 地方,日付 as 入り, 開け, 期間
FROM
(select split_part("列名",'_',1)  地方,年, 日付,split_part("列名",'_',2) as 入り開け,
lag(日付) OVER (PARTITION BY split_part("列名",'_',1), 年 ORDER BY split_part("列名",'_',2) desc) as 開け,
(lag(日付) OVER (PARTITION BY split_part("列名",'_',1), 年 )  - 日付) as 期間 
from 梅雨_t
order by 地方, 年,split_part("列名",'_',2)) as T
WHERE 期間 IS NOT NULL
ORDER BY 期間 desc
limit 10;
```

:::note warn
[windows関数](https://www.postgresql.jp/document/8.4/html/tutorial-window.html)を使ってみた。
aliasが途中で使えないので、みやすくないですね。
:::