土日・祝祭日をカウントしないクエリ

土日、祝祭日をカウントしないで、経過日数から処理を行いたい。

休日テーブルのようなものを作って、LEFTJOINして加工
のようなことをやっていたが、
DBなくても動くものが欲しかったので、調べてみたオボエガキ。

範囲を絞る方法として、
次のように使う

select 【id】 from 【table名】
where (to_timestamp(to_char(now(),'YYYY-MM-DD'),'YYYY-MM-DD') -
(to_timestamp(to_char(【table名】.【受付日】,'9999-99-99'),'YYYY-MM-DD'))) > interval '5 days'
and
 (SELECT count(*) FROM generate_series(to_timestamp(to_char(【table名】.【受付日】,'9999-99-99'),'YYYY-MM-DD'),
to_timestamp(to_char(now(),'YYYY-MM-DD'),'YYYY-MM-DD')-interval  '1 day',
interval  '1 day') the_day
WHERE (extract('ISODOW' FROM the_day) < 6
and not(EXTRACT(MONTH FROM the_day) * 100
+ EXTRACT(DAY FROM the_day)
IN (101, 113, 211, 223, 224, 320, 429, 503, 504, 505, 506, 723, 724, 810, 921, 922, 1103, 1123) ) ) ) >4

idが 【table名】.【受付日】から5日以上経過（当日含まないので中4日）
なお、条件として、【table名】.【受付日】がint2(8)の為、DATE型に変換している。

解説
副問い合わせをしているのだが、

(SELECT count(*) FROM generate_series(to_timestamp(to_char(【table名】.【受付日】,'9999-99-99'),'YYYY-MM-DD'),
to_timestamp(to_char(now(),'YYYY-MM-DD'),'YYYY-MM-DD')-interval  '1 day',
interval  '1 day') the_day
WHERE (extract('ISODOW' FROM the_day) < 6
and not(EXTRACT(MONTH FROM the_day) * 100
+ EXTRACT(DAY FROM the_day)
IN (101, 113, 211, 223, 224, 320, 429, 503, 504, 505, 506, 723, 724, 810, 921, 922, 1103, 1123) ) ) ) >4

 generate_series　という集合を返す関数を使って、カウントが中4日となる様に制御している。

 generate_series
(
to_timestamp(to_char(【table名】.【受付日】,'9999-99-99'),'YYYY-MM-DD'),
to_timestamp(to_char(now(),'YYYY-MM-DD'),'YYYY-MM-DD')-interval  '1 day',
interval  '1 day'
) the_day

始まり,終わり,間隔　と記載している。　
終わりの中で -interval  '1 day'　としているのは、当日を入れたくないため。

WHERE
(
extract('ISODOW' FROM the_day) < 6
and not
(
EXTRACT(MONTH FROM the_day) * 100
+
EXTRACT(DAY FROM the_day)
IN
 (101, 113, 211, 223, 224, 320, 429, 503, 504, 505, 506, 723, 724, 810, 921, 922, 1103, 1123)
)

extract('ISODOW' FROM the_day) < 6　ISODOWは6が土曜、7が日曜となる。
(DOWだと0が日曜、6が土曜)

and not　以下の括りで、国民の休日をカウント対象から除外させている。

EXTRACT(MONTH FROM the_day) * 100
+
EXTRACT(DAY FROM the_day)
で月を100倍し、日と足す。
これを　副問い合わせのIN関数
（左側の式の結果が右側の式のどれかと等しい場合、結果は「真」を返す）
を用いて、その日付だった場合は、2重否定となり、カウントされない。


固定日以外の春分の日なんかは、国立天文台の算定式で括ってあげればいいんだが、
このところ、天皇即位やオリンピックで変則が多いので、調べて直接日付を書いたほうが
よさそう。

春分の計算
(EXTRACT(MONTH FROM date) = 3
AND EXTRACT(DAY FROM date) = FLOOR(20.8431
+ 0.242194 * (EXTRACT(YEAR FROM date) - 1980))
- FLOOR((EXTRACT(YEAR FROM date) - 1980) / 4))



and not　でそれぞれ括れば、除外できるが

and not が直ぐに思いつかなかった・・・。

postgrers シーケンス nextvalの記述と確認と変更

シーケンス（シーケンシャル、連番）作成
create sequence seq_id start with 1;

現在のシーケンス確認 
SELECT last_value from seq_id;

nextvalの値を確認
select nextval('seq_id');

値を修正
SELECT SETVAL('seq_id',999);

SQL CASE文 複数記述 三つの内最大の物を取得する

次のようなテーブル名　Scoreで4行の其々最大のレコードを取得したいとき。　

    
Score
      

  
x
  
y
    
z
  
1
  
2
    
3
  
  
1
  
2
    
2
  
  
3
  
3
    
3
  
  
5
  
4
    
2
  

単純な比較の場合は、簡単な記述で済む

SELECT CASE WHEN x < y THEN y
            ELSE x END AS maxmum
  FROM Score;

しかしながら、複数で比較が発生する場合は些か記載が面倒になる

SELECT CASE WHEN CASE WHEN x < y THEN y
                      ELSE x END < z THEN z
            ELSE CASE WHEN x < y THEN y
                      ELSE x END END AS maxmum

  FROM Score;

4つでの比較になると、更に2回記述が増えるので、非常に見難い。

三つ巴までが使用限界と思う。

テーブル内に処理番号、日付、店舗ごとの管理連番、を格納している社内DBで、

年度ごとの店舗ごとの処理件数の合計をカウントでなく、管理連番の最大値の合計を

取得したかった。

受付時、受付受理時、一次納品、二次納品、三次納品、納品完了

このようなステージごとに管理しているが、

一次～三次納品は一括りで数値が欲しかったため、

複数のcaseが必要になった。

力技で一行づつ比較となるので、

レコード数が重いと結構負荷がかかるようだ。

PostgreSQL 配列関数 複数行帰ってくるクエリを一行で表示させる表現 ARRAY関数

複数のテーブルを跨いだクエリで、selectの結果が複数行で返ってくる場合、
pg_fetch_array等で、一覧表として表現したい事がある。

通常、LEFT JOIN等を行っても、複数行がそのまま表示されてしまう。
ただ内容を見る場合は、これでも良いのだが、
pg_num_rows　等で件数を合わせて管理したい場合、カウントがおかしくなってしまう。

いろいろと探したところ、配列関数でクエリを投げれるようなので、実装した際のオボエガキ。

条件

テーブル
tab1
tab2

tab1　のカラム
seqnumber
name
add
・
・

tab2　のカラム
tab2seqnumber
number　こいつもユニークでこれを軸に番号を発番している。
memo
・
・

クエリ生成

select seqnumber,
( select array_to_string(ARRAY(select number||memo from tab2
where tab1.seqnumber=tab2.tab2seqnumber ), ',') ) AS combine,name,add from tab1 where seqnumber=7777;

このような表現でOK

大枠のクエリの抽出条件の中に、
select array_to_string(ARRAY・・・, ',' ）を入れ子にしているのがポイント。

たとえば、
select　seqnumber,number||memo,name,add from  tab1 left join tab2 on tab1.seqnumber=tab2.tab2seqnumber where seqnumber=7777;

この場合、3行の検索結果が返ってくるとすると、
seqnumber,101memo1,102memo2,103memo3,name,add
のように
number||memo が若い順にカンマ区切りで文字列として表示される。

何気にすごく使える。

本家の解説ページ
http://www.postgresql.jp/document/8.0/html/functions-array.html

ExcelでpostgreSqlに接続しsqlを利用

手順のオボエガキ

PostgreSQLのODBCドライバをインストール
　OS（32bit）　Excel(32bit)
　32bitのPostgreSQLのODBCドライバをダウンロードしインストール

　OS(64bit)　Excel(64bit)
　64bitのPostgreSQLのODBCドライバをダウンロードしインストール

　OS(64bit)　Excel(32bit)
　32bitのPostgreSQLのODBCドライバをダウンロードしインストール
　管理者権限でコマンドプロンプトを起動
　C:\Windows\SysWOW64\odbcad32.exe　を叩き起動。
　32bitのODBCドライバを追加する。 

Excelでの設定
　データTAB→その他のデータソース→データ接続ウイザード
　→ODBC　DSN→PostgreSQLのデータソースを選択

　適宜SQLを編集し、指定のセルにクエリ結果を表示させる。

以上

ODBC DSNで接続環境を作らないと不安定な接続となったので、
この方法を取る事。

postgresql 他の拠点（ホスト）からのアクセスを許可する設定

postgresの

/usr/local/pgsql/data/postgresql.conf　を修正

listen_addresses = '*'
コメントになっている場合は、コメントを消す（＃を取る）

接続ホストの制限をしている設定ファイル
/usr/local/pgsql/data/pg_hba.conf　を修正

末尾に新しい拠点（ホスト）を記載。

host    all         all         192.168.XXX.0/24       password

補足

サブネットマスクは忘れず記載。 

passwordで　md5ベースのパスワード

md5は総当たりでやられるらしく、

SHA-2とかのほうがセキュアらしいが、

その辺はルーターで穴を塞いでいるので、

ローカルレベルのパスワード定義で十分。

# /etc/rc.d/init.d/postgresql restart
でpostgresqlを再起動して、設定を有効にする。