S3に配置したJSON形式のアンケート回答データサンプルを、Athenaから参照してクロス集計するSQLを例示します。複数の設問を軸にして、全設問をクロス集計してみます。
クロス集計とは、こんな感じの表のことです。（集計結果は適当に私が入力したものなので参考にしないでください！）
この表でいう軸の設問は、未婚既婚、性別、年代の3つとなります。
| あなたはTVを見ますか？ | 未婚 | 既婚 | ||||||||||
| 男性 | 女性 | 男性 | 女性 | |||||||||
| 20代 | 30代 | 40代 | 20代 | 30代 | 40代 | 20代 | 30代 | 40代 | 20代 | 30代 | 40代 | |
| 全体(人) | 130 | 128 | 132 | 126 | 134 | 130 | 127 | 130 | 132 | 126 | 130 | 128 |
| よく見る | 45% | 63% | 58% | 53% | 60% | 49% | 40% | 55% | 50% | 60% | 58% | 47% |
| たまに見る | 30% | 24% | 25% | 40% | 38% | 36% | 45% | 38% | 37% | 28% | 30% | 43% |
| あまり見ない | 25% | 13% | 17% | 8% | 2% | 15% | 15% | 7% | 13% | 12% | 12% | 10% |
本記事のポイント
- Athenaでサブクエリをつかって、Array形式の列を縦持ちに分解する方法
- 上記クエリの中で、軸の情報を各設問と紐づける方法
前提（JSON構造）
JSONは以下のような構成になっていて、1行1回答者のレコードとなります。1行の中に10問の設問への回答がARRAY（配列）として格納されています。ここでは軸1=Q1,軸2=Q2,軸3=Q3とします。
{
"id":"654",
"answers":
[
{ "question":"Q1", "choiced":1 },
{ "question":"Q2", "choiced":1 },
{ "question":"Q3", "choiced":3 },
{ "question":"Q4", "choiced":3 },
{ "question":"Q5", "choiced":1 },
{ "question":"Q6", "choiced":3 },
{ "question":"Q7", "choiced":1 },
{ "question":"Q8", "choiced":8 },
{ "question":"Q9", "choiced":2 },
{ "question":"Q10", "choiced":9 }
]
}
idが回答者番号（ユニークKeyとなる）、answersが設問と回答の配列です。
questionが設問番号で、choicedが選択した選択肢番号です。
Athena側のテーブル定義
上記をGlueで読み込んで作られた定義です。(PartitionはS3のディレクトリに紐づけています)
surveys
|
#
|
Column name
|
Data type
|
Partition key
|
Comment
|
| 1 | id | string | – | – |
| 2 | answers | array | – | – |
| 3 | survey | string | Partition (1) | – |
Athenaで作成したSQL
まず1行1回答者のレコードを、設問ごとの盾持ち構造にしたうえで、軸となる設問を対象の設問に紐づくようにサブクエリで構成します。そのうえで、軸毎にGroupingします。
WITH exploded_answers AS (
--answersを分解
SELECT
id,
e.question,
e.choiced,
MAX(CASE WHEN e.question= 'Q1' THEN e.choiced END) OVER (PARTITION BY id) AS axis1,
MAX(CASE WHEN e.question= 'Q2' THEN e.choiced END) OVER (PARTITION BY id) AS axis2,
MAX(CASE WHEN e.question= 'Q3' THEN e.choiced END) OVER (PARTITION BY id) AS axis3
FROM
surveys
CROSS JOIN UNNEST(answers) AS t(e)
WHERE
survey = 'survey0001'
)
--軸1,軸2,軸3,設問毎の集計
SELECT
question, choiced, axis1, axis2, axis3, COUNT(*) AS count
FROM
exploded_answers
GROUP BY
axis1, axis2, axis3, question, choiced
ORDER BY
question, choiced,axis1, axis2, axis3
解説
Array形式の項目をSQL内で取扱いできるようにするには「UNNEST」でeに代入します。
CROSS JOIN UNNEST(answers) AS t(e)
サブクエリでは、idというユニークキーをパーティションとして軸設問を抽出し、各設問と紐付けます。
MAX(CASE WHEN e.question= 'Q1' THEN e.choiced END) OVER (PARTITION BY id) AS axis1,
MAX(CASE WHEN e.question= 'Q2' THEN e.choiced END) OVER (PARTITION BY id) AS axis2,
MAX(CASE WHEN e.question= 'Q3' THEN e.choiced END) OVER (PARTITION BY id) AS axis3
| id | 設問(question) | 選択肢(choiced) | 軸1(axis1) | 軸2(axis2) | 軸3(axis3) |
| 654 | Q1 | 1 | 1 | 1 | 3 |
| 654 | Q2 | 1 | 1 | 1 | 3 |
| 654 | Q3 | 3 | 1 | 1 | 3 |
| 654 | Q4 | 3 | 1 | 1 | 3 |
| 654 | Q5 | 1 | 1 | 1 | 3 |
| 654 | Q6 | 3 | 1 | 1 | 3 |
| : | : | : | : | : |
あとはCountし、軸を含めてGroup Byで集計します。(組み合わせが存在する場合に集計した結果行が取得できます。)
| 設問(question) | 選択肢(choiced) | 軸1(axis1) | 軸2(axis2) | 軸3(axis3) | count(集計) |
| Q1 | 1 | 1 | 1 | 1 | 78 |
| Q1 | 1 | 1 | 1 | 2 | 3 |
| Q1 | 1 | 1 | 1 | 3 | 45 |
| Q1 | 1 | 1 | 2 | 1 | 34 |
| Q1 | 1 | 1 | 2 | 2 | 24 |
| Q1 | 1 | 1 | 3 | 1 | 9 |
| Q1 | 1 | 1 | 3 | 2 | 30 |
| Q1 | 1 | 2 | 1 | 1 | 10 |
| : | : | : | : | : | : |
最後に
AthenaのSQLで得た結果を配列で処理し、ああしてこうしてこねくりまわして冒頭のクロス集計表に編集します。
