仕事メモとか

仕事中に調べた情報とか知ったことをメモしています。
unixコマンド, vim, oracle, putty, postgresql, bash, EXCEL, python, SQL全般 など。
最近は tableau, movabletype とかも触ったりしています。
雑な読書感想とかはこちら

カテゴリ: PostgreSQL


PostgreSQLで複数行の連番のレコードを作る方法

かなり特殊な要件ですが、1~6の行データが存在するはずなのに、
4までしかないとか、5までしかない、っていうものがありまして。

その場合、欠損行は欠損として出す必要がありました。

ということで、こんな感じでやってみました。

generate_series
を使って連番のデータを作ります。

select generate_series( 1, 6 )

これにデータを模したやつをぶつけてみます。

SELECT gs, coalesce(dat_name,'--nodata--')
from generate_series( 1, 6 ) gs
left outer join (select 1 as dat_no, 'aaa'::text as dat_name
union all
select 2 as dat_no, 'bbb'::text as dat_name
) dat on (dat.dat_no = gs)


他にもいい方法はあると思いますが、generate_seriesは使いこなすと便利そう。


参考:
https://language-and-engineering.hatenablog.jp/entry/20100309/p1


PostgreSQLで同じSQLをループで複数回使ってみる(with recursive)

はい、基礎的なSQLを作ってみたのですが、
それを複数の日付で何回も実行したい、と思いまして。

SQLでそもそも出来ないかなーと思ったら、ちょうどいいのがありました。

例)

with recursive seq(i) as (
select 0
union all
select i + 1 from seq where i < 3
)
select cast('2018-01-01' as timestamp) + cast(i || ' day' as interval)
as target_date
from seq;

結果)
2018-01-01 00:00:00.0
2018-01-02 00:00:00.0
2018-01-03 00:00:00.0
2018-01-04 00:00:00.0


with recursiveでループ部分を作り(上記だと0~3までの数字をiに返す)、
その内容をcastして日付に足した値を返します。

もう少しちゃんと作ったほうが良いのかもしれませんが、
雑な調査とかならこれで十分いけそうです。


PostgreSQLで日付の操作(1か月前とか)

はい、oracleに慣れ過ぎていて、postgreSQLの構文が覚えられません。
ということでメモ。

1か月後とか2か月後といった入力は、intervalを使うと見やすくなります。

select to_date('20180101', 'YYYYMMDD') - interval '1 month'

これで2018/01/01の1か月前。
マイナスをプラスにすれば1か月後。

monthの部分は

day
week
month
year

ただ、これ続きがあって、date型ではなくtimestamp型だとちょっと挙動が変わる。

select cast('2018-01-01' as timestamp) + '-1 months'


時間が無いのとあまり使わないのでちゃんと調べないのですが、
そのうち何がどうなのか調べようかと思います。



参考:
http://neos21.hatenablog.com/entry/2017/05/04/080000
http://sak.cool.coocan.jp/w_sak3/doc/sysbrd/psql_k16.htm
https://www.postgresql.jp/document/9.4/html/datatype-datetime.html


failed to find conversion function from unknown to text が出た

はい、postgresqlの話です。
SQLを見ていたら、不思議な構文がありました。


例)

select '' || 'DATA' ...

メモに、
「failed to find conversion function from unknown to text」の回避とありました。

このエラー調べてみたら、定数を入れてたんだけど型がわからん、というものでした。

例)
select 'DATA'


ツールとかで実行すると、型不明なので、?column? と出ます。
この場合、この定数の文字はテキスト側だよ、と明示指定する必要があります。

select 'DATA'::text

一番最初のやつは、||で繋げてるから文字型だろう、とpostgresqlが判断したため、
あれでも回避できるようですが、あんまり綺麗じゃないので定数を入れるときには型指定しましょう。




参考:
https://jikkenjo.net/2084.html
https://www.postgresql.jp/document/9.4/html/sql-createcast.html


postgresqlでlistaggみたいなことを探してみた

string_agg関数で行けるそうです。

postgresqlで、Ver9から実装されたようなので、凄い古いバージョン使っている場合には、
他の記載方法で対応してみてください(下の参考サイト)

●使い方
select string_agg(dat,'_')
from (
select 'a' as dat
union
select 'b' as dat
) as dat_table

●順番を指定したい場合
order by を使う

select string_agg(dat,'_' order by dat desc)
from (
select 'a' as dat
union
select 'b' as dat
) as dat_table


b_a



参考:
https://lets.postgresql.jp/documents/technical/9.0/1

↑このページのトップヘ