場所分雷-復習12
3317 ワード
年齢別に区分する
mst usersテーブル.
WITH mst_users_with_int_birth_date AS (
SELECT *
, 20170101 AS int_specific_date
, CAST(REPLACE(SUBSTRING(birth_date, 1, 10), '-', '') AS integer) AS int_birth_date
FROM mst_users
)
, mst_users_with_age AS (
SELECT *
, FLOOR((int_specific_date-int_birth_date)/10000) AS age
FROM mst_users_with_int_birth_date
)
SELECT user_id
, sex
, birth_date
, age
FROM mst_users_with_age
REPLACE:置換する文字列と置換する文字を入力し、置換する文字を入力します.
CAST:データ型を他のデータ型に変換する関数.
この2つの関数を使用して、特定の日付でユーザーの年齢を求めることができます.
このWITH文を使用して、年齢区分を追加できます.
WITH mst_users_with_int_birth_date AS (
SELECT *
, 20170101 AS int_specific_date
, CAST(REPLACE(SUBSTRING(birth_date, 1, 10), '-', '') AS integer) AS int_birth_date
FROM mst_users
)
, mst_users_with_age AS (
SELECT *
, FLOOR((int_specific_date-int_birth_date)/10000) AS age
FROM mst_users_with_int_birth_date
)
, mst_users_with_category AS (
SELECT user_id
, sex
, age
, CONCAT(CASE WHEN 20<=age THEN sex
ELSE '' END
, CASE WHEN age BETWEEN 4 AND 12 THEN 'C'
WHEN age BETWEEN 13 AND 19 THEN 'T'
WHEN age BETWEEN 20 AND 34 THEN '1'
WHEN age BETWEEN 35 AND 49 THEN '2'
WHEN age >= 50 THEN '3'
END) AS category
FROM mst_users_with_age)
SELECT *
FROM mst_users_with_category
CONCAT:文字列を結合する役割を果たします.
年齢別フィーチャーの抽出
今回はaction log表とJOINがそれぞれ購入した商品カテゴリを集計します.
WITH mst_users_with_int_birth_date AS (
SELECT *
, 20170101 AS int_specific_date
, CAST(REPLACE(SUBSTRING(birth_date, 1, 10), '-', '') AS integer) AS int_birth_date
FROM mst_users
)
, mst_users_with_age AS (
SELECT *
, FLOOR((int_specific_date-int_birth_date)/10000) AS age
FROM mst_users_with_int_birth_date
)
, mst_users_with_category AS (
SELECT user_id
, sex
, age
, CONCAT(CASE WHEN 20<=age THEN sex
ELSE '' END
, CASE WHEN age BETWEEN 4 AND 12 THEN 'C'
WHEN age BETWEEN 13 AND 19 THEN 'T'
WHEN age BETWEEN 20 AND 34 THEN '1'
WHEN age BETWEEN 35 AND 49 THEN '2'
WHEN age >= 50 THEN '3'
END) AS category
FROM mst_users_with_age)
SELECT p.category AS product_category
, u.category AS user_category
, COUNT(*) AS purchase_count
FROM action_log AS p
JOIN mst_users_with_category AS u
ON p.user_id = u.user_id
WHERE action = 'purchase'
GROUP BY p.category, u.category
ORDER BY p.category, u.category
Reference
この問題について(場所分雷-復習12), 我々は、より多くの情報をここで見つけました https://velog.io/@gugu_dragon/데.분.레-복습-12テキストは自由に共有またはコピーできます。ただし、このドキュメントのURLは参考URLとして残しておいてください。
Collection and Share based on the CC Protocol