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

发布于:2025-03-17 ⋅ 阅读:(13) ⋅ 点赞:(0)

sql

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)查询累积授信总次数、当日授信总次数、当日授信总人数、当日授信但未支用人数。

还款计划表: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 的作用

 有 PARTITION BY name, subject 的 SQL 查询结果

没有 PARTITION BY 的 SQL 查询结果

excel

题目1 

计算总分

  • 在“总分”列的第一个单元格中输入公式:

    =C2+D2+E2

    这里假设C2、D2、E2分别是语文、数学、英语的成绩。

  • 按回车键,Excel会自动计算出总分。

  • 将这个单元格的公式向下拖动,应用到其他学生的总分计算中

题目2 

假设数据源如下:  

姓名 科目 成绩
Sam 语文 85
Sam 数学 90
Sam 英语 88
Jack 语文 78
Jack 数学 82
Jack 英语 80
Rose 语文 92
Rose 数学 95
Rose 英语 90
Courtney 语文 88
Courtney 数学 85
Courtney 英语 87

 

题目3

假设数据源如下: 

在 A1:E5 区域

最后填充结果 

题目4

解析:

创建透视表

设置透视表字段

设置小数位数

任意一个单元格>>右键>>数字格式>>数值的小数位数

 

题目5

解析


网站公告

今日签到

点亮在社区的每一天
去签到