论坛 › 数据仓库与分析 › 数据开辟岗笔试题＞＞sql(hive) ,excel [2025]

金歌 发表于 2025-3-24 02:15:11

数据开辟岗笔试题＞＞sql(hive) ,excel [2025]

sql

https://i-blog.csdnimg.cn/direct/4d4053f8ff0e4a2f9cab2d6e4853a08e.png
SELECT user_id, AVG(loan_amount) AS avg_loan_amount
FROM loan
GROUP BY user_id
HAVING AVG(loan_amount) > 20000; 授信表：credit
字段包罗user_id(用户id)，credit_id(授信id)，credit_time(授信时间yyyy-MM-dd HH:mm:ss)，credit_status(授信状态)。
支用表：loan
字段包罗user_id(用户id)，loan_id(订单id)，credit_id(授信id)，loan_time(支用时间yyyy-MM-dd HH:mm:ss)，loan_amount(支用金额)。
1）查询累积授信总次数、当日授信总次数、当日授信总人数、当日授信但未支用人数。
https://i-blog.csdnimg.cn/direct/730ffcb7260744618d75ba9780f087f7.png
还款计划表：repay_plan;
字段包罗loan_order_id(订单号), duration_num(期数), plan_begin_time(计划还款开始时间), plan_end_time(计划还款竣事时间), principal(应还金额)。
还款流水表：repay_detail;
字段包罗loan_order_id(订单号), real_repay_time(实际还款时间), repay_amount(实际还款金额)。
1).查询每个订单按时还款、1-15日内还款、15日+还款的还款金额；
2).查询每个订单近3日还款金额、当月还款金额、近3个月还款金额；

1).查询每个订单按时还款、1-15日内还款、15日+还款的还款金额；
SELECT
    rp.loan_order_id AS order_id,
    SUM(CASE
            WHEN rd.real_repay_time BETWEEN rp.plan_begin_time AND rp.plan_end_time
            THEN rd.repay_amount ELSE 0
      END) AS on_time_repay_amount, -- 按时还款金额
    SUM(CASE
            WHEN rd.real_repay_time > rp.plan_end_time
            AND DATEDIFF(rd.real_repay_time, rp.plan_end_time) BETWEEN 1 AND 15
            THEN rd.repay_amount ELSE 0
      END) AS within_15_days_repay_amount, -- 1-15日内还款金额
    SUM(CASE
            WHEN rd.real_repay_time > rp.plan_end_time
            AND DATEDIFF(rd.real_repay_time, rp.plan_end_time) > 15
            THEN rd.repay_amount ELSE 0
      END) AS over_15_days_repay_amount -- 15日+还款金额
FROM
    repay_plan rp
LEFT JOIN
    repay_detail rd
ON
    rp.loan_order_id = rd.loan_order_id
GROUP BY
    rp.loan_order_id; 2).查询每个订单近3日还款金额、当月还款金额、近3个月还款金额；
SELECT
    rd.loan_order_id AS order_id,
    SUM(CASE
            WHEN rd.real_repay_time >= DATE_SUB(CURRENT_DATE(), 2)
            THEN rd.repay_amount ELSE 0
      END) AS last_3_days_repay_amount, -- 近3日还款金额
    SUM(CASE
            WHEN DATE_FORMAT(rd.real_repay_time, 'yyyy-MM')
               = DATE_FORMAT(CURRENT_DATE(), 'yyyy-MM')
            THEN rd.repay_amount ELSE 0
      END) AS current_month_repay_amount, -- 当月还款金额
    SUM(CASE
            WHEN rd.real_repay_time >= DATE_SUB(CURRENT_DATE(), 90)
            THEN rd.repay_amount ELSE 0
      END) AS last_3_months_repay_amount -- 近3个月还款金额
FROM
    repay_detail rd
GROUP BY
    rd.loan_order_id; 请用一段sql代码取出每位学生末了一场语文考试的成绩和末了一场数学考试的成绩(假设同一科目同天考试次数<=1次)
表名：student_score 每一行数据代表某位学生在某一天某门学科的考试成绩
字段：name(姓名)，subject（科目），score（分数），date（考试日期,yyyy-mm-dd）
WITH last_exam AS (
    SELECT
      name,
      subject,
      score,
      date,
      ROW_NUMBER() OVER (PARTITION BY name, subject ORDER BY date DESC) AS rn
    FROM
      student_score
    WHERE
      subject IN ('语文', '数学')
)
SELECT
    name,
    MAX(CASE WHEN subject = '语文' AND rn = 1 THEN score END) AS last_chinese_score,
    MAX(CASE WHEN subject = '数学' AND rn = 1 THEN score END) AS last_math_score
FROM
    last_exam
GROUP BY
    name; 剖析: hql有partition by 的作用
https://i-blog.csdnimg.cn/direct/02174aa34cd046499119ca79cb627ca8.png
 有 PARTITION BY name, subject 的 SQL 查询结果
https://i-blog.csdnimg.cn/direct/db45c6c325f1469996f706cc46e70280.png
没有 PARTITION BY 的 SQL 查询结果
https://i-blog.csdnimg.cn/direct/e7ecb1f7bcda43eba1511b28204abdef.png

excel

标题1 

https://i-blog.csdnimg.cn/direct/fe31d9caf628427faf0fdba4e8cb5717.png
盘算总分：


[*] 在“总分”列的第一个单元格中输入公式：
=C2+D2+E2 这里假设C2、D2、E2分别是语文、数学、英语的成绩。
[*] 按回车键，Excel会自动盘算出总分。
[*] 将这个单元格的公式向下拖动，应用到其他学生的总分盘算中
标题2 

https://i-blog.csdnimg.cn/direct/13ca640531b64b3bab1d3741c5e6ed86.png
假设数据源如下:  
姓名科目成绩Sam语文85Sam数学90Sam英语88Jack语文78Jack数学82Jack英语80Rose语文92Rose数学95Rose英语90Courtney语文88Courtney数学85Courtney英语87 https://i-blog.csdnimg.cn/direct/c835a0cea511434388eaf8c9749ded9d.png
https://i-blog.csdnimg.cn/direct/1aabb4b6cff04335bd460742e24c7c85.png
https://i-blog.csdnimg.cn/direct/50756b93e1984773b2368f14c2a700af.png
https://i-blog.csdnimg.cn/direct/ee626b57563b4c7a94d83d914dfd4c2c.png
 https://i-blog.csdnimg.cn/direct/0bcf08eab6d64641b319f850aa9dbe37.png
标题3

https://i-blog.csdnimg.cn/direct/c0f8d399217842d0bc6b67c8ede0a4f3.png
假设数据源如下: 
在 A1:E5 区域
https://i-blog.csdnimg.cn/direct/5c50842b3234470ea50bf042a27ea356.png
https://i-blog.csdnimg.cn/direct/ecb2e3dc013c44d1aa15a4beb8adaaff.png
末了填充结果 

标题4

https://i-blog.csdnimg.cn/direct/a2f72ce5ea88469584a942adf024e2c5.png
剖析:
创建透视表
https://i-blog.csdnimg.cn/direct/7c4d8e8af5bc444e83f2939f643a9801.png
https://i-blog.csdnimg.cn/direct/f216aecf7ef341d5a33185e52ccc3fa5.png
设置透视表字段
https://i-blog.csdnimg.cn/direct/c9570593bcbf413095ea1961edd21fcc.png
https://i-blog.csdnimg.cn/direct/92e0cf203b5a406198b2a223f5f3e689.png
设置小数位数
恣意一个单元格>>右键>>数字格式>>数值的小数位数
 https://i-blog.csdnimg.cn/direct/6f69dc06bae54f70a9939d44a95c1b38.png
https://i-blog.csdnimg.cn/direct/a31c560f9aa94c25ab8fbd7384ce5b1b.png
https://i-blog.csdnimg.cn/direct/beb31fc0d37449b1bb175043073f1837.png
标题5

https://i-blog.csdnimg.cn/direct/0be70aae9bd34b74bfb76132b361622d.png
剖析
https://i-blog.csdnimg.cn/direct/fcf32067d35242a1817911e340d2bec2.png

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。

查看完整版本: 数据开辟岗笔试题＞＞sql(hive) ,excel [2025]