LoginSignup
0

More than 1 year has passed since last update.

PostgreSQL を Power BI のデータソースとした際の備忘録

Posted at

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

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
0