【トレジャーデータ】WITH句を使って複雑なSQLの可読性・効率性を改善

前回書いた複雑なSQL
をWITH句を用いて効率性を改善！ただ、どのくらい改善したか計測してない。。。


-- WITH句を使うことで効率性改善
WITH my_with AS(
  SELECT
    article_name,
    user_id
  FROM
    データベース名.mysite_log
  WHERE
-- NOT LIKE '%Safari%'を使っていたが遅くなると聞き下記に変更
    td_browser != 'Safari'
    AND TD_TIME_RANGE(time,
      '2016-06-01',
      '2016-07-01',
      'JST')
  GROUP BY
    article_name,
    user_id
) SELECT
  mysite.article_name,
  MAX(mysite.my_uu) AS my_uu,
  MAX(othersite.other_uu) AS other_uu,
  MAX(order.order_uu) AS order_uu
FROM (
    SELECT
      article_name,
      COUNT(DISTINCT user_id) AS my_uu
    FROM
      my_with
    GROUP BY
      article_name
  ) mysite LEFT
JOIN (
    SELECT
      article_name,
      COUNT(DISTINCT imid) AS other_uu
    FROM
      my_with
    WHERE
      user_id IN(
        SELECT
          user_id
        FROM
          データベース名.log_web_wp
        WHERE
          TD_TIME_RANGE(time,
            '2016-06-01',
            '2016-07-01',
            'jst')
      )
    GROUP BY
      article_name
  ) othersite
  ON (
    mysite.td_title = othersite.td_title
  ) LEFT
JOIN (
    SELECT
      article_name,
      COUNT(DISTINCT user_id) AS order_uu
    FROM
      my_with
    WHERE
      user_id IN(
        SELECT
          user_id
        FROM (
            SELECT
              user_id,
              order_id
            FROM
              データベース名.userid_orderid_matching
          ) matching
        JOIN (
            SELECT
              order_id
            FROM
              データベース名.order_log
            WHERE
              TD_TIME_RANGE(time,
                '2016-06-01',
                '2016-07-01',
                'jst')
              )
          ) order_log
          ON (
            matching.order_id = order_log.order_id
          )
      )
    GROUP BY
      article_name
  ) order
  ON (
    mysite.article_name = order.article_name
  )
GROUP BY
  mysite.article_name
ORDER BY
  mysite_uu DESC