用SQL实现一些需求

_BuLuLU

已于 2024-06-19 15:49:33 修改

阅读量132

点赞数

分类专栏： SQL 文章标签： sql 数据库 postgresql

于 2023-04-27 15:23:21 首次发布

本文链接：https://blog.csdn.net/qq_37531186/article/details/130369847

版权

SQL 专栏收录该内容

3 篇文章 0 订阅

订阅专栏

文章目录

场景一
- 需求一
- 需求二
场景二
场景三
场景四

根据工作中遇到的需要用sql实现的问题，在此记录。持续更新，如果您有更简洁的实现方式不妨留言😊。

场景一

借贷场景，一张借贷表，一张还款明细表。
数据库使用PostgreSQL14。

测试表

-- 简单测试，不涉及利息
create table db_test.t_loan (
    c_bh varchar(32),    --编号
    c_name varchar(100), --姓名
    n_amount numeric(20,2),
    d_start date,
    d_end date);

insert into db_test.t_loan
    values (replace(uuid_generate_v4()::text,'-',''), '张三',1000.00,'2022-01-01','2022-03-31')
        ,(replace(uuid_generate_v4()::text,'-',''), '李四',2000.00,'2022-10-01','2022-12-31')
        ,(replace(uuid_generate_v4()::text,'-',''), '王五',5000.00,'2023-01-01','2022-02-28');


create table db_test.t_loan_detail (
    c_bh varchar(32),
    c_loan_bh varchar(32),
    d_success date,
    n_repay numeric(20,2)
);

insert into db_test.t_loan_detail
    values (replace(uuid_generate_v4()::text,'-',''), '93c61bb5e264407cbeb13386fed4862e','2022-02-03',200.00)
        ,(replace(uuid_generate_v4()::text,'-',''), '93c61bb5e264407cbeb13386fed4862e','2022-03-31',200.00)
        ,(replace(uuid_generate_v4()::text,'-',''), '93c61bb5e264407cbeb13386fed4862e','2023-02-03',600.00)
        ,(replace(uuid_generate_v4()::text,'-',''), '352516d5b2cd4cc7934bfc094c3cbbba','2022-12-01',2000.00)
        ,(replace(uuid_generate_v4()::text,'-',''), '14b80f5a42a3467cbb93b215e990dd09','2023-01-01',2000.00)
        ,(replace(uuid_generate_v4()::text,'-',''), '14b80f5a42a3467cbb93b215e990dd09','2023-02-01',2000.00)
        ,(replace(uuid_generate_v4()::text,'-',''), '14b80f5a42a3467cbb93b215e990dd09','2023-02-02',2000.00);

需求一

获取每次还款时间距离上次还款的天数（第一次还款距离开始借款天数）。

--v2版本 实现sql
SELECT t1.c_loan_bh, t1.d_success,t2.c_name,t2.n_amount,t2.d_start,t2.d_end,t1.n_repay
       ,lag(t1.d_success) OVER (PARTITION BY t1.c_loan_bh ORDER BY t1.d_success),
       t1.d_success - case when (lag(t1.d_success) OVER (PARTITION BY t1.c_loan_bh ORDER BY t1.d_success)) is null then t2.d_start
 else lag(t1.d_success) OVER (PARTITION BY t1.c_loan_bh ORDER BY t1.d_success) end as n_day
FROM db_test.t_loan_detail t1
JOIN db_test.t_loan t2 ON t1.c_loan_bh = t2.c_bh
order by t2.d_start,t1.d_success;

--V1版本 实现sql
--select t1.c_bh,t1.c_name,t1.n_amount,t1.d_start,t1.d_end,t2.n_repay
--     ,t2.d_success as d_success1,t3.d_success as d_success2
--    ,case when t3.d_success is null then t2.d_success - t1.d_start
--        else t2.d_success - t3.d_success end  as n_day --每次还款时间距离上次还款天数（第一次还款距离开始借款天数）
--from db_test.t_loan t1
--left join (select c_loan_bh,d_success,n_repay,row_number() over (partition by c_loan_bh order by d_success) as nu
--           from db_test.t_loan_detail) t2 on t1.c_bh=t2.c_loan_bh
--left join (select c_loan_bh,d_success,row_number() over (partition by c_loan_bh order by d_success) as nu
--           from db_test.t_loan_detail) t3 on t2.c_loan_bh = t3.c_loan_bh and t2.nu = (t3.nu + 1) 
--order by t1.d_start,t2.d_success;

结果

编号	姓名	借款金额	开始日期	结束日期	本次还款金额	本次还款时间	上次或开始借款日期	距上次还款时间
93c61bb5e264407cbeb13386fed4862e	张三	1000.00	2022-01-01	2022-03-31	200.00	2022-02-07	null	37
93c61bb5e264407cbeb13386fed4862e	张三	1000.00	2022-01-01	2022-03-31	200.00	2022-03-31	2022-02-07	52
93c61bb5e264407cbeb13386fed4862e	张三	1000.00	2022-01-01	2022-03-31	600.00	2023-02-04	2022-03-31	310
352516d5b2cd4cc7934bfc094c3cbbba	李四	2000.00	2022-10-01	2022-12-31	2000.00	2022-12-01	null	61
14b80f5a42a3467cbb93b215e990dd09	王五	5000.00	2023-01-01	2022-02-28	2000.00	2023-01-08	null	7
14b80f5a42a3467cbb93b215e990dd09	王五	5000.00	2023-01-01	2022-02-28	2000.00	2023-02-02	2023-01-08	25
14b80f5a42a3467cbb93b215e990dd09	王五	5000.00	2023-01-01	2022-02-28	2000.00	2023-02-05	2023-02-02	3

需求二

计算每次还款后，还剩多少未还本金。

--同一笔贷款计算，多笔计算需要用到存储过程或函数循环编号
select aa.*,  n_amount - sum(n_repay) over (order by c_loan_bh,nu) as n_unpaid_amt --累加
from (select t1.c_loan_bh,t2.c_name, t1.d_success,t2.n_amount,t1.n_repay,
       row_number() over (partition by t1.c_loan_bh order by d_success) as nu
from db_test.t_loan_detail t1
left join db_test.t_loan t2 on t1.c_loan_bh=t2.c_bh
where c_loan_bh='14b80f5a42a3467cbb93b215e990dd09'
)aa;

结果

编号	姓名	还款日期	借款金额	本次还款金额	还款顺序	未还本金
14b80f5a42a3467cbb93b215e990dd09	王五	2023-01-08	5000.00	2000.00	1	3000
14b80f5a42a3467cbb93b215e990dd09	王五	2023-02-02	5000.00	2000.00	2	1000
14b80f5a42a3467cbb93b215e990dd09	王五	2023-02-05	5000.00	1000.00	3	0

场景二

创建测试数据。需要在1、2、3三个数中随机输出一个数，比例分别为10%、30%、60%。

先说我听到以上需求时的想法和做法：

1、先找有没有相关函数，很遗憾没找到。
2、自己构造一个包含1、2、3的表，比例分别为10%、30%、60%，在随机取这个表的数据。

其实就是想复杂了，以下实现为ChatGPT对我的降维打击：

SELECT CASE 
         WHEN random() <= 0.1 THEN 1
         WHEN random() <= 0.4 THEN 2
         ELSE 3
       END AS num;

场景三

查询第二名的成绩

--测试数据
db_test=# select * from db_test.t_score;
 id | name | score
----+------+-------
  1 | 张三 |    60
  2 | 李四 |    80
  4 | 王五 |   100
  5 | 赵六 |    80
(4 rows)
--一个窗口函数即可
db_test=# SELECT name, score, rank() OVER (ORDER BY score desc) AS rank
db_test-# from db_test.t_score ;
 name | score | rank
------+-------+------
 王五 |   100 |    1
 李四 |    80 |    2
 赵六 |    80 |    2
 张三 |    60 |    4
(4 rows)

场景四

如下需求，一列数字，降序取排名前10%的数据

create table public.t_test
(
    id    integer,
    n_num integer
);

postgres=# select * from public.t_test;
 id | n_num
----+-------
  1 |     1
  2 |     2
  3 |     3
  4 |     4
  5 |     5
  6 |     6
  7 |     7
  8 |     8
  9 |     9
 10 |    10
 11 |     1
 12 |     2
  1 |     1
(13 rows)

计算

postgres=# select id,n_num,round(cume_dist() over (order by n_num)::numeric,6)
postgres-# from t_test
postgres-# order by n_num desc;
 id | n_num |  round
----+-------+----------
 10 |    10 | 1.000000
  9 |     9 | 0.923077
  8 |     8 | 0.846154
  7 |     7 | 0.769231
  6 |     6 | 0.692308
  5 |     5 | 0.615385
  4 |     4 | 0.538462
  3 |     3 | 0.461538
  2 |     2 | 0.384615
 12 |     2 | 0.384615
  1 |     1 | 0.230769
  1 |     1 | 0.230769
 11 |     1 | 0.230769
(13 rows)

各个数据库基本都有此函数，一些低版本数据库包括分布式数据库可能没有，可以用如下方式计算

postgres=# select id,n_num,
postgres-#        round(sum(cnt) over (order by n_num ) / sum(cnt) over (),6) as rate
postgres-# ,sum(cnt) over (order by n_num )
postgres-# ,sum(cnt) over ()
postgres-# from (
postgres(#          select id,n_num, count(*) over() as cnt
postgres(#          from t_test
postgres(#      )aaa
postgres-# order by n_num desc;
 id | n_num |   rate   | sum | sum
----+-------+----------+-----+-----
 10 |    10 | 1.000000 | 169 | 169
  9 |     9 | 0.923077 | 156 | 169
  8 |     8 | 0.846154 | 143 | 169
  7 |     7 | 0.769231 | 130 | 169
  6 |     6 | 0.692308 | 117 | 169
  5 |     5 | 0.615385 | 104 | 169
  4 |     4 | 0.538462 |  91 | 169
  3 |     3 | 0.461538 |  78 | 169
  2 |     2 | 0.384615 |  65 | 169
 12 |     2 | 0.384615 |  65 | 169
  1 |     1 | 0.230769 |  39 | 169
  1 |     1 | 0.230769 |  39 | 169
 11 |     1 | 0.230769 |  39 | 169