【postgres】group by 〜 first row 集計して最初の行をとる
select * from detail;
product_id | item | value
------------+------+--------------
000001 | 01 | 1111-1111-11
000001 | 02 | 100000
000001 | 03 | 100000
000001 | 04 | in
000001 | 05 | 100000
000002 | 01 | 2222-1111-11
000002 | 02 | 100000
000002 | 03 | 100000
000002 | 04 | out
000002 | 05 | 100000
000003 | 01 | 3333-1111-11
000003 | 02 | 100000
000003 | 03 | 100000
000003 | 04 | in
000003 | 05 | 100000
(15 rows)
select *,rank() over (partition by product_id
order by item desc ) as rank
from detail;
product_id | item | value | rank
------------+------+--------------+------
000001 | 05 | 100000 | 1
000001 | 04 | in | 2
000001 | 03 | 100000 | 3
000001 | 02 | 100000 | 4
000001 | 01 | 1111-1111-11 | 5
000002 | 05 | 100000 | 1
000002 | 04 | out | 2
000002 | 03 | 100000 | 3
000002 | 02 | 100000 | 4
000002 | 01 | 2222-1111-11 | 5
000003 | 05 | 100000 | 1
000003 | 04 | in | 2
000003 | 03 | 100000 | 3
000003 | 02 | 100000 | 4
000003 | 01 | 3333-1111-11 | 5
(15 rows)
あとはrank=1のものに条件を絞ればOK
【Postgresql】 テーブル全体を固定長データとして出力するサンプル
サンプルテーブルの構造は以下の通り
select * from nyukintable;
id | siten | kokyakuid | nyukin | torihikibi
----+-------+-----------+--------+------------
1 | 東京 | 001 | 77500 | 2007-01-01
2 | 大阪 | 002 | 56200 | 2007-01-02
3 | 福岡 | 003 | 17900 | 2007-01-03
4 | 東京 | 004 | 27000 | 2007-01-04
5 | 大阪 | 005 | 71000 | 2007-01-05
6 | 福岡 | 006 | 13100 | 2007-01-06
7 | 東京 | 007 | 680 | 2007-01-07
8 | 大阪 | 008 | 7600 | 2007-01-08
9 | 福岡 | 009 | 61600 | 2007-01-09
10 | 東京 | 010 | 28400 | 2007-01-10
１つの文字列に変換します
ついでにヘッダーもつけてみます
WITH read_str AS (
SELECT rpad(nyukintable.id::text, 3, ' '::text) AS id,
rpad(nyukintable.siten::text, 5, ' '::text) AS siten,
rpad(nyukintable.kokyakuid::text, 8, ' '::text) AS kokyakuid,
rpad(nyukintable.nyukin::text, 10, ' '::text) AS nyukin
FROM nyukintable
), fix_header AS (
SELECT '0001AAAABBBBCC'::text AS head
), data_rec AS (
SELECT array_to_string(array_agg(ARRAY[read_str.id, read_str.siten, read_str.kokyakuid, read_str.nyukin]), ''::text) AS hurikomi
FROM read_str
)
SELECT fix_header.head || data_rec.hurikomi
FROM fix_header,
data_rec;
結果
------------------------------------
0001AAAABBBBCC1 東京 001 77500 2 大阪 002 56200 3 福岡 003 17900 4 東京 004 27000 5 大阪 005 71000 6 福岡 006 13100 7 東京 007 680 8 大阪 008 7600 9 福岡 009 61600 10 東京 010 28400 11 大阪 011 32700 12 福岡 012 46500 13 東京 013 10200 14 大阪 014 43000 15 福岡 015 48200 16 東京 016 83700 17 大阪 017 92400 18 福岡 018 17800 19 東京 019 91400 20 大阪 020 70300 21 福岡 021 37900 22 東京 022 3700 23 大阪 023 1900 24 福岡 024 91700 25 東京 025 6800 26 大阪 026 96600 27 福岡 027 55900 28 東京 028 65800 29 大阪 029 74400 30 福岡 030 76400 31 東京 031 90400 32 大阪 032 4300 33 福岡 033 16100 34 東京 034 63700 35 大阪 035 64000 36 福岡 036 83600 37 東京 037 19900 38 大阪 038 84900 39 福岡 039 3400 40 東京 040 16100 41 大阪 041 97400 42 福岡 042 1800 43 東京 043 1500 44 大阪 044 94100 45 福岡 045 62500 46 東京 046 4100 47 大阪 047 46200 48 福岡 048 51900 49 東京 049 7800 50 大阪 050 41200
【Postgresql】 重複排除と先頭取得の併用(distinct on & lead)
サンプルテーブルの構造です
ch_dateはレート(rate)の変更を行った日付ですが、
レートの適用期間を算出したい(yyyy-mm-dd〜yyyy-mm-dd 0.001%)
a=# select * from rate_history order by ch_date;
ch_date | rate
---------------------+-----------
2021-02-15 00:00:00 | 0.0008000
2021-03-01 00:00:00 | 0.0010000
2021-03-02 00:00:00 | 0.0010000
2021-03-03 00:00:00 | 0.0010000
2021-03-04 00:00:00 | 0.0020000
2021-03-05 00:00:00 | 0.0020000
2021-03-06 00:00:00 | 0.0020000
2021-03-07 00:00:00 | 0.0030000
2021-03-08 00:00:00 | 0.0030000
2021-03-09 00:00:00 | 0.0040000
(10 rows)
変更日付でソート＆重複除外します
# select ch_date,rate from
(select distinct on (rate) ch_date,rate from (select * from rate_history order by ch_date)a)c;
ch_date | rate
---------------------+-----------
2021-02-15 00:00:00 | 0.0008000
2021-03-01 00:00:00 | 0.0010000
2021-03-04 00:00:00 | 0.0020000
2021-03-07 00:00:00 | 0.0030000
2021-03-09 00:00:00 | 0.0040000
(5 rows)
期間の終了日を次の変更日から１日前を計算して取得します
この行方向の移動にlead関数を利用します
# select
ch_date,
coalesce(
lead(ch_date,1,null) over (order by ch_date) + cast('-1 days' as interval) ,
ch_date) as end_date,
rate
from
(
select distinct on (rate) ch_date,rate from
(select * from rate_history order by ch_date)a
)c;
ch_date | end_date | rate
---------------------+---------------------+-----------
2021-02-15 00:00:00 | 2021-02-28 00:00:00 | 0.0008000
2021-03-01 00:00:00 | 2021-03-03 00:00:00 | 0.0010000
2021-03-04 00:00:00 | 2021-03-06 00:00:00 | 0.0020000
2021-03-07 00:00:00 | 2021-03-08 00:00:00 | 0.0030000
2021-03-09 00:00:00 | 2021-03-09 00:00:00 | 0.0040000
(5 rows)
【Postgresql】集計して、CSV形式にする方法
select * from nyukintable;
id | siten | kokyakuid | nyukin | torihikibi
----+-------+-----------+--------+------------
1 | 東京 | 001 | 77500 | 2007-01-01
2 | 大阪 | 002 | 56200 | 2007-01-02
3 | 福岡 | 003 | 17900 | 2007-01-03
4 | 東京 | 004 | 27000 | 2007-01-04
5 | 大阪 | 005 | 71000 | 2007-01-05
6 | 福岡 | 006 | 13100 | 2007-01-06
7 | 東京 | 007 | 680 | 2007-01-07
8 | 大阪 | 008 | 7600 | 2007-01-08
select siten,sum(nyukin) from nyukintable group by siten;
siten | sum
-------+--------
東京 | 598680
福岡 | 686300
大阪 | 978200
select array_to_string(array_agg(array[siten,sums]),',') from (select siten,cast(sum(nyukin) as text) as sums from nyukintable group by siten) a;
array_to_string
-------------------------------------
東京,598680,福岡,686300,大阪,978200
(1 row)
【Postgresql】総合計を計算する
テーブルの構造とデータの状態です
select * from bs_summary ;
agg_code | debit | credit
----------+---------+--------
1002 | 1000000 |
1001 | 1000000 |
2001 | | 800000
2002 | | 800000
(4 rows)
各列の合計を最後に出力する
select coalesce(agg_code,'9999') as agg_code,
sum(debit) as debit,
sum(credit) as credit
from bs_summary
group by rollup(agg_code)
order by agg_code;
agg_code | debit | credit
----------+---------+---------
1001 | 1000000 |
1002 | 1000000 |
2001 | | 800000
2002 | | 800000
9999 | 2000000 | 1600000
(5 rows)
【Postgresql】 generate_seriesを使ったシーケンス（日付）
select
*
from GENERATE_SERIES('2020-03-12'::timestamp, '2030-03-12'::timestamp, '6 month'::INTERVAL);
generate_series
---------------------
2020-03-12 00:00:00
2020-09-12 00:00:00
2021-03-12 00:00:00
2021-09-12 00:00:00
2022-03-12 00:00:00
2022-09-12 00:00:00
2023-03-12 00:00:00
2023-09-12 00:00:00
2024-03-12 00:00:00
2024-09-12 00:00:00
2025-03-12 00:00:00
2025-09-12 00:00:00
2026-03-12 00:00:00
2026-09-12 00:00:00
2027-03-12 00:00:00
2027-09-12 00:00:00
2028-03-12 00:00:00
2028-09-12 00:00:00
2029-03-12 00:00:00
2029-09-12 00:00:00
2030-03-12 00:00:00
(21 rows)
