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