1. baku2san

    Posted

    baku2san
Changes in title
+PostgreSQL を Power BI のデータソースとした際の備忘録
Changes in tags
Changes in body
Source | HTML | Preview

PostgreSQL

View 生成などの SQL とか

全Table削除 SQL

Create 用のSQLが更新して渡されてきたので、SQL実行前に Schema 毎削除する為

Schema毎削除
DROP SCHEMA public CASCADE;
CREATE SCHEMA public;

日付(startdatetime)⇒日付(startdate)・時刻分割(starttime)

BI で日付は分割したほうがいいよって記事があったので。

https://qiita.com/PowerBIxyz/items/c12416169f13093f6e8a

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対処用。

https://www.postgresql.jp/document/9.4/html/functions-conditional.html

連続値を生成して、固定データを追加

範囲型とか、集合データの返却とか、便利なものがあるんだなと勉強になりました
日付なんかも連続生成できるとかも素敵ですね

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

https://www.postgresql.jp/document/13/html/functions-srf.html

LinqPad

Sample Data の生成を C# で書きたかったので、以下を使って操作した際の注意点

https://qiita.com/baku2san/items/6a98e24bcfc55e2f5122

型相違対処

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