PostgreSQL
View 生成などの SQL とか
全Table削除 SQL
Create 用のSQLが更新して渡されてきたので、SQL実行前に Schema 毎削除する為
Schema毎削除
DROP SCHEMA public CASCADE;
CREATE SCHEMA public;
日付(startdatetime)⇒日付(startdate)・時刻分割(starttime)
BI で日付は分割したほうがいいよって記事があったので。
pgAdmin[コード]
table.startdatetime::date AS startdate,
table.startdatetime::time without time zone AS starttime,
CreateQuery
CAST(public.table.startdatetime AS DATE) as StartDate,
CAST(public.table.startdatetime AS DATE) as StartTime,
時刻差分抽出
差分が必要だったので
pgAdmin[コード]
(table.enddatetime - table.startdatetime)::time without time zone AS difftime,
CreateQuery
CAST((public.table.enddatetime - public.table.startdatetime) AS Time) as DiffTime,
集計後結合
SQL 書いてないとこういうの大変・・。今回も LinqPad で書いて雛型 SQL にしました
条件式もついつい、If 書いちゃうしね・・
あとでパフォーマンス分析はしないと・・
集計と結合
SELECT
t1.ID,
t1.WorkTime,
t1.RestTime,
machine.Name
FROM (
SELECT t.ID,
COALESCE(SUM(CASE WHEN t0.status <= 4 THEN t0.time END), 0)::INT AS WorkTime,
COALESCE(SUM(CASE WHEN t0.status > 4 THEN t0.timeEND), 0)::INT AS RestTime
FROM table AS t
INNER JOIN tabletime AS t0 ON t.ID = t0.ID
GROUP BY t.ID
) AS t1
INNER JOIN machine ON t1.MachineID = machine.ID
COALESCE は、Null対処用。
連続値を生成して、固定データを追加
範囲型とか、集合データの返却とか、便利なものがあるんだなと勉強になりました
日付なんかも連続生成できるとかも素敵ですね
Sample
SELECT
ids as ID,
ids as Percentage
FROM generate_series(1,4) as ids
UNION
SELECT
serial as ID,
serial * 2.5 as Percentage
FROM generate_series(5,10, 1) as serial
ORDER BY ID
LinqPad
Sample Data の生成を C# で書きたかったので、以下を使って操作した際の注意点
型相違対処
PostgreSQL | C# | 対処 | 例 |
---|---|---|---|
Bigint | Int64 | ||
int | Integer | ||
BigSerial | Int64 | ||
Serial | Integer | ||
bit | BitArray | 16進指定⇒BitArray定義 | new BitArray(new bool[]{true}) |
Real | Single | Suffix "F" を付ける。 | 2.5F |