本文是PostgreSQL Advent Calendar 2022的第六天。

这篇文章是之前由藤井正男先生撰写的文章。

最初

正在使用的是PostgreSQL

为了学习SQL(PostgreSQL)，有一些可以在线学习的地方，比如psql-basics。不过，屏幕有点小。

这次我想在我熟悉的Google Colaboratory上试一试。

代码

%%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会下载版本12。
为了下载最新版本，我将按照官方网站的指示进行操作。

关于密码设置等事项，我是从Google Colaboratory的教程中获取的。

输入数据

# 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)

我可以自动从带有标题的CSV文件中创建一个表格吗？

数据来源于加利福尼亚州旧金山湾区的天气数据的Pandas + Seaborn教程。

由于通过to_sql()函数简单地读取数据类型会变得非常奇怪，因此我进行了修正。

修正数据类型的方法

# 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和pandas.DataFrame.to_sql。

考试

%%sql
select * from weather limit 10;

事情顺利进行了。

关于psql

只要运行sudo -u postgres psql -U postgres -d tutorial，基本上就会出现psql提示符，但无法看到输入值，并且只能以简单方式使用。

有没有什么好的方法呢？

总结

暂时来说，准备完毕了。

我们在继续进行时，边阅读各种书籍。

“赠品” or “附加赠品”

在这项练习中要使用的邮政编码数据。

由于将PDF文档复制粘贴时会出现偏移的情况。

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

!unzip ken_all.zip

%%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

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

做得非常漂亮。

奖品2

既经将其加入到Advent Calender中，但还想要更多的数据。
还有一些气象厅的数据，试了一下结果却是失败了・・・・orz

数据处理和导入

# データの入手
#　昭和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

select * from 梅雨 limit 5;

好在最后解决了。

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

冲绳的梅雨季很长，对吧？

然而，事实上哪里是最长的呢？

确认梅雨季节的时间

%config SqlMagic.autopandas = False

%%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

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

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;

以前九州的梅雨似乎很长。

%%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;