Qiita Teams that are logged in
You are not logged in to any team

Log in to Qiita Team
Community
OrganizationEventAdvent CalendarQiitadon (β)
Service
Qiita JobsQiita ZineQiita Blog
0
Help us understand the problem. What are the problem?

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

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
Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
0
Help us understand the problem. What are the problem?