{"id":3413,"date":"2024-06-13T08:30:44","date_gmt":"2024-06-12T23:30:44","guid":{"rendered":"https:\/\/www.uncovertruth.co.jp\/dx-accelerator\/?p=3413"},"modified":"2024-09-27T17:23:24","modified_gmt":"2024-09-27T08:23:24","slug":"054","status":"publish","type":"post","link":"https:\/\/www.uncovertruth.co.jp\/dx-accelerator\/blog\/articles\/bigquery\/054\/","title":{"rendered":"\u3010BigQuery\u3011SQL\u3067\u6708\u3054\u3068\u306b\u9867\u5ba2\u30e9\u30f3\u30af\u3092\u3082\u3064\u30af\u30a8\u30ea\u3092\u4f5c\u6210\u3059\u308b\u65b9\u6cd5"},"content":{"rendered":"\n

<\/p>\n\n\n\n

\u306f\u3058\u3081\u306b<\/h2>\n\n\n\n

\u793e\u5185\u696d\u52d9\u306e\u4e2d\u3067\u3001\u6708\u3054\u3068\u306b\u9867\u5ba2\u30e9\u30f3\u30af\u3092\u3082\u3064\u30c7\u30fc\u30bf\u30de\u30fc\u30c8\u3092\u4f5c\u6210\u3059\u308b\u6a5f\u4f1a\u304c\u3042\u308a\u307e\u3057\u305f\u3002\u9867\u5ba2\u30e9\u30f3\u30af\u306f\u30de\u30fc\u30b1\u30c6\u30a3\u30f3\u30b0\u306e\u4e2d\u3067\u3082\u91cd\u8981\u306a\u6307\u6a19\u306e\u3072\u3068\u3064\u306b\u306a\u308b\u3053\u3068\u304c\u591a\u3044\u306e\u3067\u3001\u53c2\u8003\u306b\u306a\u308c\u3070\u3068\u601d\u3044\u307e\u3059\u3002<\/p>\n\n\n\n

<\/p>\n\n\n\n

SQL\u3067\u6708\u3054\u3068\u306b\u9867\u5ba2\u30e9\u30f3\u30af\u3092\u6301\u3064\u30c6\u30fc\u30d6\u30eb\u3092\u4f5c\u6210\u3059\u308b\u65b9\u6cd5<\/h2>\n\n\n\n

\u4eca\u56de\u306fArray\u3092\u7528\u3044\u3066\u8cfc\u8cb7\u306e\u306a\u3044\u6708\u306e\u6b20\u640d\u30c7\u30fc\u30bf\u3092\u88dc\u3044\u3064\u3064\u3001\u5404\u6708\u306e\u9867\u5ba2\u30e9\u30f3\u30af\u3092\u7b97\u51fa\u3059\u308b\u30c7\u30fc\u30bf\u30c6\u30fc\u30d6\u30eb\u3092\u4f5c\u6210\u3057\u307e\u3059\u3002<\/p>\n\n\n\n

<\/p>\n\n\n\n

\u9867\u5ba2\u30e9\u30f3\u30af\u306e\u5b9a\u7fa9\u306b\u3064\u3044\u3066<\/mark><\/h3>\n\n\n\n

\u4eca\u56de\u306f\u4e0b\u8a18\u306e\u3088\u3046\u306a\u5b9a\u7fa9\u3067\u9867\u5ba2\u30e9\u30f3\u30af\u3092\u8a2d\u5b9a\u3057\u307e\u3057\u305f\u3002
\u30af\u30a8\u30ea\u3092\u53c2\u8003\u306b\u3059\u308b\u5834\u5408\u306f\u3001\u6700\u5f8c\u306e\u30e9\u30f3\u30af\u5224\u5b9a\u6642\u306b\u5fc5\u8981\u306b\u5fdc\u3058\u3066\u30e9\u30f3\u30af\u6761\u4ef6\u3092\u5909\u66f4\u3057\u3066\u304f\u3060\u3055\u3044\u3002<\/p>\n\n\n\n

\u25bc\u9867\u5ba2\u30e9\u30f3\u30af<\/strong><\/p>\n\n\n\n

\u8cfc\u5165\u7dcf\u56de\u6570\/\u8cfc\u5165\u7dcf\u91d1\u984d<\/th>4,500\u5186\u672a\u6e80<\/th>4,500\u5186\u4ee5\u4e0a~19,800\u5186\u672a\u6e80<\/th>19,800\u5186\u4ee5\u4e0a<\/th><\/tr>
2\u56de\u4ee5\u4e0a<\/th>E<\/td>C<\/td>A<\/td><\/tr>
1\u56de<\/th>F<\/td>D<\/td>B<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n

<\/p>\n\n\n\n

\n

\u9867\u5ba2\u30e9\u30f3\u30af\u3068\u306f<\/strong><\/p>\n\n\n\n

\u9867\u5ba2\u30e9\u30f3\u30af\u3068\u306f\u3001\u9867\u5ba2\u3092\u8cfc\u8cb7\u884c\u52d5\u3084\u5b9f\u7e3e\u306b\u5fdc\u3058\u3066\u30e9\u30f3\u30af\u4ed8\u3051\uff08\u91cd\u307f\u4ed8\u3051\uff09\u3092\u3059\u308b\u3053\u3068\u3092\u3044\u3044\u307e\u3059\u3002
\u4eca\u56de\u306f\u3001\u8cfc\u5165\u56de\u6570\uff08Frequency\uff09\u3068\u8cfc\u5165\u91d1\u984d\uff08Monetary\uff09\u3067\u30e9\u30f3\u30af\u4ed8\u3051\u3092\u3057\u3066\u30bb\u30b0\u30e1\u30f3\u30c8\u3092\u4f5c\u6210\u3057\u3066\u3044\u307e\u3059\u3002<\/p>\n<\/div><\/div>\n\n\n\n

\u6708\u3054\u3068\u306b\u9867\u5ba2\u30e9\u30f3\u30af\u3092\u6301\u3064\u30c6\u30fc\u30d6\u30eb\u306e\u4f5c\u6210\u30af\u30a8\u30ea\uff08\u5168\u4f53\uff09<\/mark><\/h3>\n\n\n\n

<\/p>\n\n\n

\n\/*\u6ce8\u6587\u65e5\u306eMIN\u5024\u304b\u3089MAX\u5024\u307e\u3067\u306e\u5e74\u6708\u4e00\u89a7\u3092\u4f5c\u6210*\/\nDECLARE date_array ARRAY<DATE>;\nSET date_array = GENERATE_DATE_ARRAY(\n      (SELECT DATE(FORMAT_DATE('%Y-%m', MIN(order_date)) || '-01') \n        FROM `\u30c8\u30e9\u30f3\u30b6\u30af\u30b7\u30e7\u30f3\u30c7\u30fc\u30bf\u30c6\u30fc\u30d6\u30eb`)\n      ,(SELECT DATE(FORMAT_DATE('%Y-%m', MAX(order_date)) || '-01') \n         FROM `\u30c8\u30e9\u30f3\u30b6\u30af\u30b7\u30e7\u30f3\u30c7\u30fc\u30bf\u30c6\u30fc\u30d6\u30eb`)\n      ,INTERVAL 1 MONTH\n  );\n\n  \/* customer_id\u3054\u3068\u306e\u6700\u521d\u306e\u6ce8\u6587\u6708\u3092\u62bd\u51fa *\/\nWITH _user AS(\n    SELECT\n      customer_id\n      ,DATE(FORMAT_DATE('%Y-%m', MIN(order_date)) || '-01') as min_order_date01 ---------- \u6700\u521d\u306e\u6ce8\u6587\u6708\u3092yyyy-mm-01\u306e\u5f62\u306b\u5909\u63db\n    FROM  `\u30c8\u30e9\u30f3\u30b6\u30af\u30b7\u30e7\u30f3\u30c7\u30fc\u30bf\u30c6\u30fc\u30d6\u30eb`\n    GROUP BY customer_id\n  )\n\n  \/* customer_id\u3054\u3068\u306b\u6700\u521d\u306e\u6ce8\u6587\u6708\u4ee5\u964d\u306e\u5e74\u6708\u3092\u4ed8\u4e0e *\/\n  ,_user_dt AS(\n    SELECT \n      _user.customer_id\n      ,car.dt\n    FROM (SELECT dt from UNNEST(date_array) as dt) AS car\n    INNER JOIN _user\n      ON _user.min_order_date01 <= car.dt\n  )\n\n  \/* \u9867\u5ba2\u306e\u3054\u3068\u306e\u30c8\u30e9\u30f3\u30b6\u30af\u30b7\u30e7\u30f3\u30c7\u30fc\u30bf *\/\n  ,_trn AS(\n    SELECT \n      tran_t.customer_id\n      ,DATE(FORMAT_DATE('%Y-%m', tran_t.order_date) || '-01') AS ord_month --------------- \u6ce8\u6587\u6708\u3092yyyy-mm-01\u306e\u5f62\u306b\u5909\u63db\n      ,tran_t.order_id\n      ,tran_d_t.price\n    FROM `\u30c8\u30e9\u30f3\u30b6\u30af\u30b7\u30e7\u30f3\u30c7\u30fc\u30bf\u30c6\u30fc\u30d6\u30eb` AS tran_t\n    LEFT JOIN `\u30c8\u30e9\u30f3\u30b6\u30af\u30b7\u30e7\u30f3\u8a73\u7d30\u30c6\u30fc\u30d6\u30eb` AS tran_d_t\n      ON tran_t.order_id = tran_d_t.order_id\n  )\n\n  \/* \u9867\u5ba2\u306e\u3054\u3068\u306edt\u304b\u3089\u904e\u53bb1\u5e74\u5206\u306e\u30c8\u30e9\u30f3\u30b6\u30af\u30b7\u30e7\u30f3\u30c7\u30fc\u30bf *\/\n  ,month_grouped_trn AS(\n    SELECT \n      _user_dt.customer_id\n      ,user_dt.dt\n      ,_trn.ord_month\n      ,COUNT(DISTINCT _trn.order_id) AS ord_cnt ---------------------- \u8cfc\u5165\u56de\u6570\n      ,IFNULL(SUM(_trn.price), 0) AS price\n    FROM _user_dt \n    LEFT JOIN _trn\n      ON _user_dt.customer_id = _trn.customer_id\n        AND (_trn.ord_month BETWEEN DATE_SUB(user_dt.dt, INTERVAL 11 MONTH) AND _user_dt.dt) -------- dt\u304b\u3089\u904e\u53bb1\u5e74\u5206\u306e\u30c7\u30fc\u30bf\u3092\u7d10\u3065\u3051\u308b\n    GROUP BY \n      _user_dt.customer_id\n      ,user_dt.dt\n      ,_trn.ord_month\n  )\n\n  ,_proceeds_by_customer AS(\n    SELECT\n      customer_id\n      ,dt\n      ,SUM(ord_cnt) AS ord_cnt_sum\n      ,SUM(price) AS price_sum\n    FROM month_grouped_trn\n    GROUP BY \n      customer_id\n      ,dt\n  )\n\n  -- ,customer_rank_t AS(\n    SELECT \n      customer_id\n      ,dt\n      ,ord_cnt_sum AS ord_cnt\n      ,price_sum AS price\n      ,CASE \n        WHEN ord_cnt_sum >= 2 AND price_sum >= 19800 THEN 'A'\n        WHEN ord_cnt_sum = 1 AND price_sum >= 19800 THEN 'B'\n        WHEN ord_cnt_sum >= 2 AND (price_sum BETWEEN 4500 AND 19799) THEN 'C'\n        WHEN ord_cnt_sum = 1 AND (price_sum BETWEEN 4500 AND 19799) THEN 'D'\n        WHEN ord_cnt_sum >= 2 AND price_sum < 4500 THEN 'E'\n        WHEN ord_cnt_sum = 1 AND price_sum < 4500 THEN 'F'\n      ELSE '\u4f11\u7720' END AS customer_rank\n    FROM _proceeds_by_customer\n    ORDER BY customer_id, dt\n\n<\/pre><\/div>\n\n\n
<\/p>\n\n\n\n
<\/p>\n\n\n\n
\u30af\u30a8\u30ea\u3092\u3072\u3068\u3064\u305a\u3064\u89e3\u8aac<\/mark><\/h3>\n\n\n\n