AI 助理
备案控制台
开发者社区 云计算 文章 正文

HQL 55 题【已完结】（5）

2024-06-07 59
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
简介： HQL 55 题【已完结】

HQL 55 题【已完结】（4）https://developer.aliyun.com/article/1532400

4、登录次数及交易次数统计

需求：分别从登陆明细表（user_login_detail）和配送信息表中用户登录时间和下单时间统计登陆次数和交易次数。

SELECT  coalesce(t1.user_id,t0.user_id) user_id,
    coalesce(t1.login_date,t0.order_date) login_date,
        nvl(t1.login_count,0) login_count,
        nvl(t0.order_count,0) order_count 
FROM
(   
    SELECT  user_id,
            order_date,
            count(order_date) order_count
    FROM delivery_info
    GROUP BY user_id,order_date
)t0
RIGHT JOIN
    (
     SELECT  user_id,
     date_format(login_ts,'yyyy-MM-dd') login_date,
     count(user_id) login_count
     FROM user_login_detail
     GROUP BY user_id,date_format(login_ts,'yyyy-MM-dd')
)t1
    ON t0.user_id=t1.user_id AND t1.login_date=t0.order_date
;

备注：下单的前提的登录，所以是 right join （个人感觉 full join ）更合理

4-11

1、查看每件商品的售价涨幅情况

需求：从商品价格变更明细表（sku_price_modify_detail），得到最近一次价格的涨幅情况，并按照涨幅升序排序。

SELECT  sku_id,
    price_change
FROM
(
    SELECT  sku_id,
            change_date,
            rk,
            new_price-lead(new_price,1,0) OVER(PARTITION BY sku_id ORDER BY change_date DESC) price_change
    FROM
    (
        SELECT  sku_id,
                change_date,
                new_price,
                row_number() OVER(PARTITION BY sku_id ORDER BY change_date DESC) rk
        FROM sku_price_modify_detail
    )t0
    WHERE rk<=2
)t1
WHERE rk=1;

2、同期商品售卖分析表

需求：从订单明细表（order_detail）中。求出同一个商品在2021年和2022年中同一个月的售卖情况对比。

SELECT  t0.sku_id,
    nvl(t0.`month`,t1.`month`) `month`,
        nvl(t1.2020_skusum,0) 2020_skusum,
    nvl(t0.2021_skusum,0) 2021_skusum
FROM
(
    SELECT  sku_id,
          CAST(date_format(create_date,'MM') AS bigint) `month`,
            sum(sku_num) 2021_skusum
    FROM order_detail
    WHERE date_format(create_date,'yyyy')='2021'
    GROUP BY sku_id,date_format(create_date,'MM')
)t0
FULL JOIN
(
    SELECT  sku_id,
            CAST(date_format(create_date,'MM') AS bigint) `month`,
            sum(sku_num) 2020_skusum
    FROM order_detail
    WHERE date_format(create_date,'yyyy')='2020'
    GROUP BY sku_id,date_format(create_date,'MM')
)t1 ON t0.sku_id=t1.sku_id AND t0.month=t1.month;

3、国庆期间每个品类的商品的收藏量和购买量

需求：从订单明细表（order_detail）和收藏信息表（favor_info）统计2021国庆期间，每个商品总收藏量和购买量。

SELECT  t0.sku_id,
    nvl(sku_sum,0) sku_sum,
        nvl(favor_cn,0) favor_cn
FROM
(
    SELECT  sku_id,
            sum(sku_num) sku_sum
    FROM order_detail
    WHERE create_date>='2021-10-01' AND create_date<='2021-10-07'
    GROUP BY sku_id
)t0
FULL JOIN
(
    SELECT  sku_id,
            count(*) favor_cn
    FROM favor_info
    WHERE create_date>='2021-10-01' AND create_date<='2021-10-07'
    GROUP BY sku_id
)t1
ON t0.sku_id=t1.sku_id;

4、连续签到领金币数

需求：

  • 用户每天签到可以领1金币，并可以累计签到天数，连续签到的第3、7天分别可以额外领2和6金币。
  • 每连续签到7天重新累积签到天数。
  • 从用户登录明细表中求出每个用户金币总数，并按照金币总数倒序排序。
SELECT  coalesce(t1.user_id,t2.user_id) user_id,
      IF(t2.coins IS NOT NULL,t1.coin+t2.coins,t1.coin) sum_coin_cn
FROM
-- 查询用户登录次数
(
    SELECT  user_id,
            sum(1) coin
    FROM
    (
        SELECT  DISTINCT
                user_id,
                date_format(login_ts,'yyyy-MM-dd') login_date
        FROM user_login_detail
    )t0
    GROUP BY user_id
)t1
LEFT JOIN
(
    -- 查询用户连续登录的次数所能获得的金币
    SELECT  user_id,
            IF(count(*)<7,2,8) coins
    FROM
    (
        SELECT  user_id,
                login_date,
                row_number() OVER(PARTITION BY user_id ORDER BY login_date) rk
        FROM
        (
            SELECT  DISTINCT
                    user_id,
                    date_format(login_ts,'yyyy-MM-dd') login_date
            FROM user_login_detail
        )t
    )t1
    GROUP BY user_id,date_sub(login_date,rk)
    HAVING count(*)>2
)t2
ON t1.user_id=t2.user_id
;

4-12

1、每个商品销售首年的年份、销售数量和销售金额

需求：从订单明细表(order_detail)统计每个商品销售首年的年份，销售数量和销售总额。

SELECT  sku_id,
    `year`,
    sum(price*sku_num) order_amount,
        sum(sku_num) order_num
FROM
(
  SELECT * FROM
    (
        SELECT  sku_id,
                price,
                create_date,
                sku_num
        FROM order_detail
    )t1
    RIGHT JOIN
    (
        -- 销售首年
        SELECT  sku_id,
                min(date_format(create_date,'yyyy')) `year`
        FROM order_detail
        GROUP BY sku_id
    )t2 ON t1.sku_id=t2.sku_id AND date_format(create_date,'yyyy')=`year`
)t3 GROUP BY sku_id,`year`
;

2、销售订单首购和次购分析

需求：通过商品信息表（sku_info）订单信息表（order_info）订单明细表（order_detail）分析如果有一个用户成功下单两个及两个以上的购买成功的手机订单（购买商品为xiaomi 10，apple 12，小米13）那么输出这个用户的id及第一次成功购买手机的日期和第二次成功购买手机的日期，以及购买手机成功的次数。

SELECT  user_id,
    min(create_date) first_date,
    max(create_date) last_date,
        max(rk) cn
FROM
(
    SELECT  user_id,
            sku_num,
            oi.create_date,
        row_number() OVER(PARTITION BY user_id ORDER BY oi.create_date) rk
    FROM order_info oi
    RIGHT JOIN
    (
        SELECT  od.order_id,
                od.sku_id,
                od.sku_num,
                od.create_date,name
        FROM order_detail od
        RIGHT JOIN 
        (
        SELECT sku_id,name
        FROM sku_info
        WHERE name IN ('xiaomi 10','apple 12','xiaomi 13')
        )t ON t.sku_id=od.sku_id
    )t1 ON t1.order_id=oi.order_id
)t2
GROUP BY user_id
;

3、查询有新注册用户的当天的新用户数量、新用户的第一天留存率

需求：从用户登录明细表（user_login_detail）中首次登录算作当天新增，第二天也登录了算作一日留存

SELECT  t2.register_date first_login,
    t2.register,
    CAST(t5.retents/t2.register AS DECIMAL(16,2)) retention
FROM
(
    SELECT  register_date,
            count(register_date) register
    FROM
    (
        SELECT  user_id,
                min(date_format(login_ts,'yyyy-MM-dd')) register_date
        FROM user_login_detail
        GROUP BY user_id
    )t1
    GROUP BY register_date
)t2
JOIN
(
    SELECT  first_login,
            sum(retent) retents
    FROM
    (
        SELECT  user_id,
                login_date first_login,
                IF(datediff(next_login_date,login_date)=1,1,0) retent
        FROM
        (
            SELECT  user_id,
                    login_date,
                    lead(login_date,1,'9999-12-31') OVER(PARTITION BY user_id ORDER BY login_date) next_login_date
            FROM
            (
                SELECT  user_id,
                        login_date,
                        rk
                FROM
                (
                    SELECT  DISTINCT
                            user_id,
                            date_format(login_ts,'yyyy-MM-dd') login_date,
                            row_number() OVER(PARTITION BY user_id ORDER BY date_format(login_ts,'yyyy-MM-dd')) rk
                    FROM user_login_detail
                )t1
                WHERE rk<=2
            )t2
          )t3 
          WHERE next_login_date != '9999-12-31'  
    )t4
        GROUP BY first_login
)t5
ON t5.first_login=register_date;
让线程再跑一会
目录
相关文章
让线程再跑一会
|
5月前
|
SQL HIVE
HQL 55 题【已完结】（1）
HQL 55 题【已完结】
让线程再跑一会
78 2
最好zzz
|
2月前
|
SQL 数据库 C++
SQL 执行顺序，通俗易懂！
在数据库的世界里，SQL（Structured Query Language）是我们与数据对话的桥梁。编写一个高效的SQL查询，不仅要求我们理解数据库的结构和数据模型，还需要掌握SQL的执行顺序。但往往，这个顺序与我们在编写SQL时书写的顺序并不一致，这常常让初学者感到困惑。今天，我们就来用通俗易懂的方式，揭开SQL执行顺序的神秘面纱。
最好zzz
99 2
让线程再跑一会
|
5月前
HQL 55 题【已完结】（3）
HQL 55 题【已完结】
让线程再跑一会
30 1
让线程再跑一会
|
5月前
HQL 55 题【已完结】（2）
HQL 55 题【已完结】
让线程再跑一会
29 1
让线程再跑一会
|
5月前
HQL 55 题【已完结】（4）
HQL 55 题【已完结】
让线程再跑一会
35 1
JavaPub
|
5月前
|
SQL Java 数据库连接
MyBatis SQL 批量更新（代码➕案例）
MyBatis SQL 批量更新（代码➕案例）
JavaPub
865 0
全栈技术精选
|
SQL Python
python技术面试题(七)--SQL语句
python技术面试题(七)--SQL语句
全栈技术精选
79 1
开发工程师
|
SQL 数据库
图解 SQL 执行顺序，通俗易懂！
图解 SQL 执行顺序，通俗易懂！
开发工程师
659 0
auqbllxiu
|
SQL XML 缓存
面试官问你MyBatis SQL是如何执行的？把这篇文章甩给他（一）
MyBatis 是第一个支持自定义 SQL、存储过程和高级映射的类持久框架。MyBatis 消除了大部分 JDBC 的样板代码、手动设置参数以及检索结果。MyBatis 能够支持简单的 XML 和注解配置规则。使 Map 接口和 POJO 类映射到数据库字段和记录。
auqbllxiu
169 0
面试官问你MyBatis SQL是如何执行的？把这篇文章甩给他（一）
与自己作战
|
SQL
多表查询 SQL21用户题目回答情况（三种写法）
多表查询 SQL21用户题目回答情况（三种写法）
与自己作战
158 0
多表查询 SQL21用户题目回答情况（三种写法）

热门文章

最新文章

  • 1
    Object reference not set to an instance of an object.
  • 2
    Map根据键、值进行排序
  • 3
    锐捷网络&视易K米，携手助力2016腾讯全球合作伙伴万人大会
  • 4
    Java中的定时器
  • 5
    Python正则表达式指南下下半部　
  • 6
    VirtualDesktop多媒体在何处解码?
  • 7
    文件移动脚本
  • 8
    xml在此生活
  • 9
    在不动用sp_configure的情况下，如何 =》去掉列的自增长，并保留原数据
  • 10
    聚焦技术实战！MDCC 2016 移动开发者大会盛大开幕
  • 1
    2024Mysql And Redis基础与进阶操作系列（6）作者——LJS[含MySQL 多表之一对一/多；多对多；多表联合查询等详解步骤及常见报错问题所对应的解决方法]
    32
  • 2
    猫头虎分享：鸿蒙生态带给开发者的全新机遇！轻松实现按需加载与多端适配，开发效率翻倍
    30
  • 3
    2024Mysql And Redis基础与进阶操作系列（5）作者——LJS[含MySQL DQL基本查询:select；简单、排序、分组、聚合、分组、分页等详解步骤及常见报错问题所对应的解决方法]
    25
  • 4
    理解 ES6 中的 Promise
    26
  • 5
    CSS 提高性能的方法
    23
  • 6
    CSS 预编语言的区别
    10
  • 7
    如何快速掌握 CSS 预编语言
    10
  • 8
    对 CSS 预编语言的理解
    12
  • 9
    利用缓存布局信息来减少回流和重绘的发生
    11
  • 10
    减少回流和重绘的发生
    12

    • 相关电子书

    更多
  • 低代码开发师（初级）实战教程
  • 冬季实战营第三期：MySQL数据库进阶实战
  • 阿里巴巴DevOps 最佳实践手册
    • 下一篇
    阿里云无影云电脑免费试用，最长可试用3个月