tsknd的な備忘録

再利用しそうな資材置き場

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