HiveでGROUP BYを伴うサブクエリのネストからGROUP BYのキー以外のカラムを簡単に引き回すテクニックメモ

Hiveでデータ分析をする時、ある程度厳密な結果が要求されてかつ条件が複雑な場合、私の場合気がつくとサブクエリのネストが深くなっていることが多いです。 しかもGROUP BYが途中で必要になるケースが多く何も深く考えずクエリを書いていると、GROUP BYした後にGROUP BYのキー以外のデータが必要になって、JOINが増えてクエリが複雑になりすぎてしまったり一番深いサブクエリから全てのサブクエリにカラムを追加していく必要が出てきてしまったりして、とても面倒な思いをすることがあります。 その時に私がよく使う本当にちょっとしたテクニックを自分用の備忘録として残したいと思います。 パッとネストが深いクエリが浮かんだ広告システムのデータ分析を題材とします。広告におけるユーザごとの広告クリックデータと商品購入データがあるとして、そこから広告の種類ごとに最終的に商品の購入に貢献した物をカウントすることにします（商品購入の直前となった広告クリックを見つけて、広告の種類ごとにカウント）。対象とする広告クリックデータの期間は１ヶ月とします。 メモとして残したいテクニックを含んだクエリは以下のようになります。

SELECT
  SPLIT(last_c_info, ',')[6] AS ad_type,
  COUNT(*)
FROM (
  SELECT
    first_p_info,
    MAX(c_info) AS last_c_info
  FROM (
    SELECT
      c_info,
      MIN(p_info) AS first_p_info
    FROM (
      SELECT
        CONCAT_WS(
          ',',
          click_time,
          click_id,
          audience_id,  -- User ID
          publisher_id,  -- Media Owner(ex. Blogger) ID
          advertiser_id,  -- Shop ID
          ad_id,
          ad_type
        ) c_info
      FROM
        clicks
      WHERE
        click_time >= '2019-12-01 00:00:00'
        AND click_time < '2020-01-01 00:00:00'
    ) c
    LEFT OUTER JOIN (
      SELECT
        CONCAT_WS(
          ',',
          purchase_time,
          purchase_id,
          audience_id,
          advertiser_id,
          product_id  -- Item ID
        ) p_info
      FROM
        purchases
      WHERE
        purchase_time >= '2019-12-01 00:00:00'
        AND purchase_time < '2020-01-02 00:00:00'
    ) p
    ON (c.audience_id = p.audience_id)
    WHERE
      p.purchase_time IS NOT NULL
      AND p.purchase_time >= c.click_time
      AND unix_timestamp(p.purchase_time) < unix_timestamp(c
.click_time) + 24 * 3600)  -- max duration is 24 hours
    GROUP BY
      c_info
  ) first_purchases
) last_clicks
GROUP BY
  SPLIT(last_c_info, ',')[6];

広告システムの話がメインではないので詳細は省いてポイントのみ紹介します。ポイントは以下のCONCAT_WSの部分です。

CONCAT_WS(
  ',',
  click_time,
  click_id,
  audience_id,
  publisher_id,
  advertiser_id,
  ad_id,
  ad_type
) c_info

CONCAT_WS(
  ',',
  purchase_time,
  purchase_id,
  audience_id,
  advertiser_id,
  product_id
) p_info

外側のクエリで使うカラムを全てこのようにCONCAT_WSで区切り文字付きで固めておけば必要な時にSPLITで取り出せます。MIN/MAXで比較に使いたいカラムを先頭にしておけばMIN/MAXもうまく動きます。 ただもちろん注意点がありまして、主な注意点は以下の３つです。２つ目の注意点はこの方法でなくとも考える必要はあります。

• MIN/MAXでは文字列の比較されるので数値は0埋めして桁を合わせたりする必要があります。
• 先頭のカラムが完全一致するケースは２番目以降のカラムが比較に使われてしまうので比較に使いたい順でカラムを並べる必要があります。
• SPLITして取り出す時は番号で取り出すので番号を間違えてしまった時にデバックが面倒です。

この方法以外で私がパッと思いつくのは、外側のクエリでJOINして後で必要なカラムを追加する方法、普通にCONCAT_WSなしでカラムをどんどん引き継いでいく方法です。１つ目はクエリが長く複雑になりすぎてしまい、２つ目は後で必要なカラムが増えた時に途中のサブクエリ全てに追加しないといけないので心理的にかなり面倒になってしまうという問題があります。