SSRS2016 RPlotをレポートに表示する
下記の2つを参考に、RのPLOTの出力をSSRS上で表現できる
・SQL Server 2016 R Services: Display R plots in Reporting Services
WHERE句をCASE文で条件分岐、NULL=NULLの対策例
やりたいこと：@P_1が'-'以外の値を取る時は@P_1を採用、それ以外ならwhere句からSpeciesの条件を外す
DECLARE @P1 NVARCHAR(64) SET @P1 = '-' SELECT * FROM Iris WHERE ISNULL(Species,'N') = CASE WHEN @P1 <> '-' THEN @P1 ELSE ISNULL(Species,'N') END
1.5Qベースで外れ値フラグを立てるSQLメモ
P1:category1 P2:category2
SELECT DISTINCT T1.[INDEX], T1.P1, T1.P2, T1.SepalLength, T2.P25_SL, T2.P75_SL, T2.[P75-P25_SL], T2.QL_SL, T2.QH_SL, T1.SepalWidth, T2.P25_SW, T2.P75_SW, T2.[P75-P25_SW], T2.QL_SW, T2.QH_SL, CASE WHEN (T1.SepalLength < T2.QL_SL) OR (T2.QH_SL < T1.SepalLength) THEN '1' WHEN (T1.SepalWidth < T2.QL_SW) OR (T2.QH_SW < T1.SepalWidth) THEN '2' ELSE '0' END AS OUTLIER FROM [NorthwindJ].[dbo].[output] AS T1 INNER JOIN (SELECT DISTINCT P1 ,P2 ,PERCENTILE_DISC(0.25) WITHIN GROUP (ORDER BY SepalLength) OVER (PARTITION BY P1,P2) AS P25_SL ,PERCENTILE_DISC(0.75) WITHIN GROUP (ORDER BY SepalLength) OVER (PARTITION BY P1,P2) AS P75_SL ,PERCENTILE_DISC(0.75) WITHIN GROUP (ORDER BY SepalLength) OVER (PARTITION BY P1,P2) - PERCENTILE_DISC(0.25) WITHIN GROUP (ORDER BY SepalLength) OVER (PARTITION BY P1,P2) AS 'P75-P25_SL' ,2.5 * PERCENTILE_DISC(0.25) WITHIN GROUP (ORDER BY SepalLength) OVER (PARTITION BY P1,P2) - 1.5 * PERCENTILE_DISC(0.75) WITHIN GROUP (ORDER BY SepalLength) OVER (PARTITION BY P1,P2) AS QL_SL , -1.5 * PERCENTILE_DISC(0.25) WITHIN GROUP (ORDER BY SepalLength) OVER (PARTITION BY P1,P2) + 2.5 * PERCENTILE_DISC(0.75) WITHIN GROUP (ORDER BY SepalLength) OVER (PARTITION BY P1,P2) AS QH_SL ,PERCENTILE_DISC(0.25) WITHIN GROUP (ORDER BY SepalWidth) OVER (PARTITION BY P1,P2) AS P25_SW ,PERCENTILE_DISC(0.75) WITHIN GROUP (ORDER BY SepalWidth) OVER (PARTITION BY P1,P2) AS P75_SW ,PERCENTILE_DISC(0.75) WITHIN GROUP (ORDER BY SepalWidth) OVER (PARTITION BY P1,P2) - PERCENTILE_DISC(0.25) WITHIN GROUP (ORDER BY SepalWidth) OVER (PARTITION BY P1,P2) AS 'P75-P25_SW' ,2.5 * PERCENTILE_DISC(0.25) WITHIN GROUP (ORDER BY SepalWidth) OVER (PARTITION BY P1,P2) - 1.5 * PERCENTILE_DISC(0.75) WITHIN GROUP (ORDER BY SepalWidth) OVER (PARTITION BY P1,P2) AS QL_SW , -1.5 * PERCENTILE_DISC(0.25) WITHIN GROUP (ORDER BY SepalWidth) OVER (PARTITION BY P1,P2) + 2.5 * PERCENTILE_DISC(0.75) WITHIN GROUP (ORDER BY SepalWidth) OVER (PARTITION BY P1,P2) AS QH_SW FROM [NorthwindJ].[dbo].[output] ) AS T2 ON T1.P1 = T2.P1 AND T1.P2 = T2.P2 WHERE T1.SepalLength < T2.QL_SL OR T2.QH_SL < T1.SepalLength OR T1.SepalWidth < T2.QL_SW OR T2.QH_SW < T1.SepalWidth ORDER BY T1.P1,T1.P2
Postgresql 改行、タブを削除する
select regexp_replace(COLUMNNAME,E'(\r\n|\r|\n|\t)',' ','g') from TABLENAME
Postgresql .batから.sqlを実行
■ バッチファイルからsqlを実行する
ここでは、postgresデータベースのirisテーブル、carsテーブルに格納されているデータをcsv形式で出力する。
・Sample1.bat
☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆
@echo off
rem PostgreSQLの場所を指定
set PGPATH=c:\"Program Files"\PostgreSQL\9.5\bin\
rem DB接続情報
rem call c:\"Program Files"\PostgreSQL\9.5\pg_env.bat
set USERID=postgres
set DBNAME=postgres
set HOST=localhost
set PORT=5432
rem ポスグレ接続
%PGPATH%psql -h %HOST% -p %PORT% -U %USERID% -d %DBNAME% -f C:/Users/XXXXX/Desktop/psql/csvout1.sql
exit
☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆
・csvout1.sql
☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆
\COPY (select * from cars) TO 'C:\Users\XXXXX\Desktop\psql\out\cars.csv' (FORMAT csv);
\COPY (select * from iris) TO 'C:\Users\XXXXX\Desktop\psql\out\iris.csv' (FORMAT csv);
☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆☆
■実行画面
Sample1.batを実行すると、パスワードが聞かれる。
おまけ
■postgres接続後にSQLを実行する場合
