SQL文（クエリ）

WITH master AS (
  SELECT
    cid
    , COUNT(*) AS pv
  FROM
    sample.web_log
  WHERE
    DATE(DATETIME_TRUNC(date_time, QUARTER)) = "2018-04-01"
  GROUP BY
    cid
)
SELECT
  cid
FROM
  (
    SELECT
      *
      , PERCENTILE_CONT(pv, 0.25) OVER () AS first_q
      , PERCENTILE_CONT(pv, 0.75) OVER () AS third_q
    FROM
      master
  )
WHERE
  pv < first_q - (third_q - first_q) * 1.5
OR pv > third_q + (third_q - first_q) * 1.5

結果テーブル

サポートページに戻る