1. baku2san

    Posted

    baku2san
Changes in title
+PostgreSQL を Power BI のデータソースとした際の備忘録
Changes in tags
Changes in body
Source | HTML | Preview
@@ -0,0 +1,95 @@
+# PostgreSQL
+View 生成などの SQL とか
+
+## 全Table削除 SQL
+Create 用のSQLが更新して渡されてきたので、SQL実行前に Schema 毎削除する為
+
+```sql:Schema毎削除
+DROP SCHEMA public CASCADE;
+CREATE SCHEMA public;
+```
+
+## 日付(startdatetime)⇒日付(startdate)・時刻分割(starttime)
+BI で日付は分割したほうがいいよって記事があったので。
+
+https://qiita.com/PowerBIxyz/items/c12416169f13093f6e8a
+
+```sql:pgAdmin[コード]
+table.startdatetime::date AS startdate,
+table.startdatetime::time without time zone AS starttime,
+```
+```sql:CreateQuery
+CAST(public.table.startdatetime AS DATE) as StartDate,
+CAST(public.table.startdatetime AS DATE) as StartTime,
+```
+
+## 時刻差分抽出
+差分が必要だったので
+
+```sql:pgAdmin[コード]
+ (table.enddatetime - table.startdatetime)::time without time zone AS difftime,
+```
+```sql:CreateQuery
+CAST((public.table.enddatetime - public.table.startdatetime) AS Time) as DiffTime,
+```
+
+## 集計後結合
+
+SQL 書いてないとこういうの大変・・。今回も LinqPad で書いて雛型 SQL にしました
+条件式もついつい、If 書いちゃうしね・・
+あとでパフォーマンス分析はしないと・・
+
+```sql:集計と結合
+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
+
+## 連続値を生成して、固定データを追加
+範囲型とか、集合データの返却とか、便利なものがあるんだなと勉強になりました
+日付なんかも連続生成できるとかも素敵ですね
+
+```sql: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|