❃博主首页 ： 「码到三十五」 ，同名公众号 :「码到三十五」，wx号 : 「liwu0213」
☠博主专栏 ： <mysql高手> <elasticsearch高手> <源码解读> <java核心> <口试攻关>
♝博主的话 ： 搬的每块砖，皆为峰峦之基；公众号搜刮「码到三十五」关注这个爱发技能干货的coder，一起筑基
电商业务,一个订单表 orders 和一个用户表 users。你需要分析用户的购买行为，特别是那些在特定时间段内购买金额超过一定阈值的用户，并计算他们的平均购买金额、购买次数以及他们在总用户中的占比。
表结构：
- 用户表 (users):
- user_id (用户ID, 主键)
- register_date (注册日期)
- city (所在都会)
- 订单表 (orders):
- order_id (订单ID, 主键)
- user_id (用户ID, 外键)
- order_date (订单日期)
- amount (订单金额)
需求：
- 找出在2023年1月1日至2023年12月31日期间，累计购买金额超过10,000元的用户。
- 计算这些用户的平均购买金额、平均购买次数。
- 计算这些高代价用户在总用户中的占比。
- 按都会分组，统计每个都会的高代价用户数量及其占比。
参考SQL：
- WITH high_value_users AS (
- SELECT
- u.user_id,
- u.city,
- SUM(o.amount) AS total_amount,
- COUNT(o.order_id) AS total_orders
- FROM
- users u
- JOIN
- orders o ON u.user_id = o.user_id
- WHERE
- o.order_date BETWEEN '2023-01-01' AND '2023-12-31'
- GROUP BY
- u.user_id, u.city
- HAVING
- SUM(o.amount) > 10000
- ),
- total_users AS (
- SELECT
- COUNT(DISTINCT user_id) AS total_user_count
- FROM
- users
- ),
- city_stats AS (
- SELECT
- city,
- COUNT(user_id) AS high_value_user_count,
- COUNT(user_id) * 1.0 / (SELECT total_user_count FROM total_users) AS high_value_user_ratio
- FROM
- high_value_users
- GROUP BY
- city
- )
- SELECT
- hvu.user_id,
- hvu.city,
- hvu.total_amount,
- hvu.total_orders,
- AVG(hvu.total_amount) OVER () AS avg_amount,
- AVG(hvu.total_orders) OVER () AS avg_orders,
- cs.high_value_user_count,
- cs.high_value_user_ratio
- FROM
- high_value_users hvu
- JOIN
- city_stats cs ON hvu.city = cs.city;
- high_value_users: 找出在2023年内累计购买金额超过10,000元的用户，并计算他们的总购买金额和订单数量。
- total_users: 计算总用户数。
- city_stats: 按都会分组，计算每个都会的高代价用户数量及其在总用户中的占比。
- 最终查询: 将高代价用户的信息与都会统计信息联合，计算平均购买金额、平均购买次数，并输出每个高代价用户的具体信息及其所在都会的高代价用户统计。
关注公众号[码到三十五]获取更多技能干货 !
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。 |
