AS( SELECT user_id, CASE WHEN COUNT(DISTINCT order_id) >= 500 THEN 'ϔϏʔ' WHEN COUNT(DISTINCT order_id) >= 300 THEN 'ϛυϧ' ELSE 'ϥΠτ' END AS hml FROM orders WHERE user_id IS NOT NULL AND order_date BETWEEN '2022-01-01' AND '2022-12-31' AND is_canceled = 0 GROUP BY user_id ) SELECT hml, COUNT(DISTINCT user_id) AS uu FROM hml_users GROUP BY hml SELECT CASE WHEN order_counts >= 500 THEN 'ϔϏʔ' WHEN order_counts >= 300 AND order_counts < 500 THEN 'ϛυϧ' ELSE 'ϥΠτ' END AS hml, COUNT(DISTINCT orders.user_id) AS user_count FROM orders JOIN ( SELECT user_id, COUNT(*) AS order_counts FROM orders JOIN products ON orders.order_product_id = products.product_id WHERE orders.is_canceled = 0 AND orders.order_date >= '2022-01-01' AND orders.order_date < '2023-01-01' GROUP BY user_id ) AS user_orders ON orders.user_id = user_orders.user_id WHERE orders.is_canceled = 0 AND orders.order_date >= '2022-01-01' AND orders.order_date < '2023-01-01' GROUP BY hml; SELECT CASE WHEN purchases_per_user.user_purchases >= 500 THEN 'ϔϏʔ' WHEN purchases_per_user.user_purchases >= 300 THEN 'ϛυϧ' ELSE 'ϥΠτ' END AS HML, COUNT(DISTINCT orders.user_id) AS user_count FROM orders INNER JOIN ( SELECT user_id, COUNT(*) AS user_purchases FROM orders WHERE orders.is_canceled = 0 AND orders.order_date BETWEEN '2022-01-01' AND '2022-12-31' GROUP BY user_id ) AS purchases_per_user ON orders.user_id = purchases_per_user.user_id INNER JOIN users ON orders.user_id = users.user_id AND users.is_deleted = 0 GROUP BY HML;
order_2022 AS ( SELECT * FROM orders WHERE order_date BETWEEN '2022-01-01' AND '2022-12-31' AND is_canceled = 0 ), -- Ϣʔβʔ͝ͱͷؒߪೖ݅ͷܭࢉ user_purchase_count AS ( SELECT user_id, COUNT(DISTINCT order_id) AS purchase_count FROM order_2022 GROUP BY user_id ), -- ϢʔβʔΛHML͝ͱʹྨ͢Δ user_hml AS ( SELECT user_id, CASE WHEN purchase_count >= 500 THEN 'ϔϏʔ' WHEN purchase_count >= 300 THEN 'ϛυϧ' ELSE 'ϥΠτ' END AS hml FROM user_purchase_count ), -- HML͝ͱͷϢʔβʔΛूܭ͢Δ hml_user_count AS ( SELECT hml, COUNT(DISTINCT user_id) AS user_count FROM user_hml GROUP BY hml ) -- ࠷ऴతͳग़ྗ݁Ռ SELECT hml, user_count FROM hml_user_count ORDER BY hml DESC WITH purchase_counts AS ( SELECT user_id, COUNT(*) AS cnt FROM orders WHERE is_canceled = 0 AND order_date BETWEEN '2022-01-01' AND '2022-12-31' GROUP BY user_id ) SELECT CASE WHEN cnt >= 500 THEN 'ϔϏʔ' WHEN cnt >= 300 THEN 'ϛυϧ' ELSE 'ϥΠτ' END AS HML, COUNT(*) AS user_count FROM purchase_counts JOIN users ON purchase_counts.user_id = users.user_id WHERE users.is_deleted = 0 GROUP BY HML;
user_count, CASE WHEN COUNT(order_id) >= 500 THEN 'ϔϏʔ' WHEN COUNT(order_id) >= 300 AND COUNT(order_id) < 500 THEN 'ϛυϧ' WHEN COUNT(order_id) < 300 THEN 'ϥΠτ' END AS category FROM orders WHERE user_id IS NOT NULL AND is_canceled = FALSE AND order_date BETWEEN '2022-01-01' AND '2022-12-31' GROUP BY category; w ΤϥʔͰ࣮ߦ݁Ռ͕ฦͬͯ͜ͳ͍ w ͨͩ͠ɺ#BSEෳͷճ͕ฦͬ ͯ͘ΔͷͰɺҰ෦ਖ਼ղฦͬͯ͘Δ º
w 42-ͷੜͳͲੳ݁Ռͷநग़$IBU(15ʹΑͬͯସ͞ΕΔՄೳੑ͋Δ $IBU(15#BSEΛਖ਼͘͠׆༻͢ΔͨΊͷೳྗ͕ඞཁ w దͳ͍Λઃఆͯ͠ϓϩϯϓτΛ͛Δٕज़ w ؒҧͬͨ42-͕ฦͬͯ͘Δ͜ͱ͋ΔͷͰɺग़ྗ͞Εͨ42-͕ਖ਼͍͔͠Λஅ͢Δ͚ͩͷೳྗ͕ඞཁ w ͢ͰʹͬͯΔͷΛΑΓޮతʹऔಘ͍ͨ͠߹ʹศར w ͦͷͨΊʹجຊతͳ42-ͳͲͷٕज़ֶΜͩํ͕Α͍ʢదͳࢦࣔΛग़ͨ͢Ίɺ݁Ռ͕ਖ਼͍͔͠Λஅ͢Δ ͨΊʣ
SiteGround - Reliable hosting with speed, security, and support you can count on.
hikarut
bengo4com
taka_aki
nyankotaro
ks91
shibayu36
shucho0103
harukasakihara
rfdnxbro
minorun365
oracle4engineer
gawa
kawaji_scratch
cassininazir
palkan
.jpg){kind=avatar}
cargoodrich
axbom
jdejongh
samanthasiow
mfonobong
coloredviolet
lemiorhan
smashingmag
pauljervisheath
zakiyama
