PostgreSQLによるデータ分析

PostgreSQL によるデータ分析

PostgreSQL　にデータを読み込みます

cd /var/tmp/
wget https://avinton.com/wp-content/uploads/2024/04/japan_cities.csv
sed 's/,/\t/g' japan_cities.csv > japan_cities.tsv
sudo su
su postgres
psql -d avinton
create table japan_cities (prefecture text, city text, ward text, population integer);
copy japan_cities from '/var/tmp/japan_cities.tsv' NULL 'NULL';
\q

cd /var/tmp/

wget https://avinton.com/wp-content/uploads/2024/04/japan_cities.csv

sed 's/,/\t/g' japan_cities.csv > japan_cities.tsv

sudo su

su postgres

psql -d avinton

create table japan_cities (prefecture text, city text, ward text, population integer);

copy japan_cities from '/var/tmp/japan_cities.tsv' NULL 'NULL';

データ整形

データ整形のため、PGAdminでPostgreSQLのデータベースに接続し、下記のコードを実行します。

create table japan_cities2 as (
select a.*, row_number() over() as id from japan_cities as a
);

drop table japan_cities;
create table japan_cities as (
select case when prefecture is null then (
select c.prefecture from japan_cities2 as c where c.id = (
select max(b.id) from japan_cities2 b where b.id < a.id and b.prefecture is not null
)) end as prefecture,(
select c.city from japan_cities2 as c where c.id = (
select max(b.id) from japan_cities2 b where b.id <= a.id and b.city is not null
)) as city, ward, population from japan_cities2 as a
);

drop table japan_cities2;

delete from japan_cities where population is null;
alter table japan_cities add column city_ward text;
update japan_cities set city_ward = 'c' where ward is null;
update japan_cities set city_ward = 'w' where ward is not null;

select * from japan_cities;

create table japan_cities2 as (

select a.*, row_number() over() as id from japan_cities as a

);

drop table japan_cities;

create table japan_cities as (

select case when prefecture is null then (

select c.prefecture from japan_cities2 as c where c.id = (

select max(b.id) from japan_cities2 b where b.id < a.id and b.prefecture is not null

)) end as prefecture,(

select c.city from japan_cities2 as c where c.id = (

select max(b.id) from japan_cities2 b where b.id <= a.id and b.city is not null

)) as city, ward, population from japan_cities2 as a

);

drop table japan_cities2;

delete from japan_cities where population is null;

alter table japan_cities add column city_ward text;

update japan_cities set city_ward = 'c' where ward is null;

update japan_cities set city_ward = 'w' where ward is not null;

select * from japan_cities;

演習

データの構造が理解できたら、下記の課題をSQLを使って表示させてみてください。

最も人口の多い区。
市の人口の標準偏差
北海道の市の数
全国の区の総数。
各県の名前とその人口
各県の人口を降順で。
各県の名前と県内で最も人口の多い市

CHALLENGE

ウィンドウ関数を用いることで次のような内容が表示できます。
・各市の名前と属す県、最も人口の少ない区と多い区、市内の総人口をそれぞれの市につき一つの行で表示

PostgreSQLによるデータ分析