计算租户的水费

最新推荐文章于 2025-05-14 11:32:26 发布

alejandro_12

最新推荐文章于 2025-05-14 11:32:26 发布

阅读量586

点赞数 2

文章标签： big data 大数据 sql

本文链接：https://blog.csdn.net/qq_43227716/article/details/121559065

版权

SQL 租户水费分摊费用计算数据处理

关键词由CSDN通过智能技术生成

故事背景:租住的房子是套四的,人员流动性也比较大,每次新租客搬入老租客搬走时总有人会对水气费用的结算产生争执,所以我写了这个代码来分析每个租客用水量,从生活出发,又回到生活中去.

计算每个租户需要结清的用水量
(每个房间无论居住几人均按一人一间计算)

当一个租户居住时,用水量完全由其一人承担,当有新租户入住时,老租户均需要结清当前用水量,
并且后续水费由此时入住的所有租户均摊
例如q在7.1入住,表值750.1,w在7.2日入住,表值760.1,则q用水量为10,w用水量=0
e在7.3入住,表值770.1,则q用水量=(10+(770.1-760.1)/2 )= 15 ,w 用水量为 (0 +(770.1-760.1)/2 ) = 15,e用水量=0
以此类推,若租户一直没搬走,止租日取结算日,表止值取当日表值,
每一次新租户的搬入及老租户的搬走,均需要在该时点进行结算

后续也有一些深思,费用的计算应该需要做拉链,不同时期的价格有上浮和下沉,计算还可以细化,

又假如这是一间酒店,酒店的房间数无穷多,计算的数据量达到TB级别时,又如何能在短时间内得到结果,代码是否可以优化,以后遇到了再想吧~~~

-- 000 原始数据

租户id   租户名称     起租日 止租日  起租日表值    止租日表值       水费单价/元
user_id  user_name    sdt    edt     sv_w          ev_w           price_w

select * from jghy;
+---------+-----------+------------+------------+--------+--------+---------+
| user_id | user_name | sdt        | edt        | sv_w   | ev_w   | price_w |
+---------+-----------+------------+------------+--------+--------+---------+
|       1 | q         | 2021-07-01 | 2021-11-01 |  950.1 | 1200.1 |     3.5 |
|       2 | w         | 2021-07-02 | NULL       |  960.1 | NULL   |     3.5 |
|       3 | e         | 2021-07-03 | 2021-10-01 |  970.1 | 1100.1 |     3.5 |
|       4 | r         | 2021-08-01 | NULL       |  975.1 | NULL   |     3.5 |
|       5 | t         | 2021-09-01 | NULL       |  980.1 | NULL   |     3.5 |
|       6 | y         | 2021-10-01 | 2021-11-15 | 1100.1 | 1250.1 |     3.5 |
|       7 | u         | 2021-09-15 | NULL       |  990.1 | NULL   |     3.5 |
+---------+-----------+------------+------------+--------+--------+---------+


-- ----------------------------
-- Table structure for jghy
-- ----------------------------
DROP TABLE IF EXISTS `jghy`;
CREATE TABLE `jghy`  (
  `user_id` int(11) DEFAULT NULL,
  `user_name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `sdt` date DEFAULT NULL,
  `edt` date DEFAULT NULL,
  `sv_w` double DEFAULT NULL,
  `ev_w` double DEFAULT NULL,
  `price_w` double DEFAULT NULL
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of jghy
-- ----------------------------
INSERT INTO `jghy` VALUES (1, 'q', '2021-07-01', '2021-11-01', 950.1, 1200.1, 3.5);
INSERT INTO `jghy` VALUES (2, 'w', '2021-07-02', NULL, 960.1, NULL, 3.5);
INSERT INTO `jghy` VALUES (3, 'e', '2021-07-03', '2021-10-01', 970.1, 1100.1, 3.5);
INSERT INTO `jghy` VALUES (4, 'r', '2021-08-01', NULL, 975.1, NULL, 3.5);
INSERT INTO `jghy` VALUES (5, 't', '2021-09-01', NULL, 980.1, NULL, 3.5);
INSERT INTO `jghy` VALUES (6, 'y', '2021-10-01', '2021-11-15', 1100.1, 1250.1, 3.5);
INSERT INTO `jghy` VALUES (7, 'u', '2021-09-15', NULL, 990.1, NULL, 3.5);

-- 001 所有租户起止时点

select distinct user_id,sdt,IFNULL(edt,(select max(edt) from jghy)) edt 
from jghy;

+---------+------------+------------+
| user_id | sdt        | edt        |
+---------+------------+------------+
|       1 | 2021-07-01 | 2021-11-01 |
|       2 | 2021-07-02 | 2021-11-15 |
|       3 | 2021-07-03 | 2021-10-01 |
|       4 | 2021-08-01 | 2021-11-15 |
|       5 | 2021-09-01 | 2021-11-15 |
|       6 | 2021-10-01 | 2021-11-15 |
|       7 | 2021-09-15 | 2021-11-15 |
+---------+------------+------------+

-- 002 所有需要进行计算的时点

select distinct sdt dt from jghy
where sdt  is not null 
union 
select distinct edt dt from jghy
where edt is not null ;

+------------+
| dt         |
+------------+
| 2021-07-01 |
| 2021-07-02 |
| 2021-07-03 |
| 2021-08-01 |
| 2021-09-01 |
| 2021-10-01 |
| 2021-09-15 |
| 2021-11-01 |
| 2021-11-15 |
+------------+

-- 003 每个租户对应的计算时点及表值

select a.user_id,b.dt,ifnull(c.sv_w,d.ev_w) val
from (select distinct user_id,sdt,IFNULL(edt,(select max(edt) from jghy)) edt from jghy) a 
left join (select distinct sdt dt from jghy
where sdt  is not null 
union 
select distinct edt dt from jghy
where edt is not null ) b  on  b.dt BETWEEN a.sdt and a.edt
left join jghy c on b.dt = c.sdt 
left join jghy d on b.dt = d.edt 
ORDER BY a.user_id,b.dt ;

+---------+------------+--------+
| user_id | dt         | val    |
+---------+------------+--------+
|       1 | 2021-07-01 |  950.1 |
|       1 | 2021-07-02 |  960.1 |
|       1 | 2021-07-03 |  970.1 |
|       1 | 2021-08-01 |  975.1 |
|       1 | 2021-09-01 |  980.1 |
|       1 | 2021-09-15 |  990.1 |
|       1 | 2021-10-01 | 1100.1 |
|       1 | 2021-11-01 | 1200.1 |
|       2 | 2021-07-02 |  960.1 |
|       2 | 2021-07-03 |  970.1 |
|       2 | 2021-08-01 |  975.1 |
|       2 | 2021-09-01 |  980.1 |
|       2 | 2021-09-15 |  990.1 |
|       2 | 2021-10-01 | 1100.1 |
|       2 | 2021-11-01 | 1200.1 |
|       2 | 2021-11-15 | 1250.1 |
|       3 | 2021-07-03 |  970.1 |
|       3 | 2021-08-01 |  975.1 |
|       3 | 2021-09-01 |  980.1 |
|       3 | 2021-09-15 |  990.1 |
|       3 | 2021-10-01 | 1100.1 |
|       4 | 2021-08-01 |  975.1 |
|       4 | 2021-09-01 |  980.1 |
|       4 | 2021-09-15 |  990.1 |
|       4 | 2021-10-01 | 1100.1 |
|       4 | 2021-11-01 | 1200.1 |
|       4 | 2021-11-15 | 1250.1 |
|       5 | 2021-09-01 |  980.1 |
|       5 | 2021-09-15 |  990.1 |
|       5 | 2021-10-01 | 1100.1 |
|       5 | 2021-11-01 | 1200.1 |
|       5 | 2021-11-15 | 1250.1 |
|       6 | 2021-10-01 | 1100.1 |
|       6 | 2021-11-01 | 1200.1 |
|       6 | 2021-11-15 | 1250.1 |
|       7 | 2021-09-15 |  990.1 |
|       7 | 2021-10-01 | 1100.1 |
|       7 | 2021-11-01 | 1200.1 |
|       7 | 2021-11-15 | 1250.1 |
+---------+------------+--------+

-- 004 每个计算时点时租户明细及表值

select GROUP_CONCAT(tmp1.user_id) users,tmp1.dt, tmp1.val from(
select a.user_id,b.dt,ifnull(c.sv_w,d.ev_w) val
from (select distinct user_id,sdt,IFNULL(edt,(select max(edt) from jghy)) edt from jghy) a 
left join (select distinct sdt dt from jghy
where sdt  is not null 
union 
select distinct edt dt from jghy
where edt is not null ) b  on  b.dt BETWEEN a.sdt and a.edt
left join jghy c on b.dt = c.sdt 
left join jghy d on b.dt = d.edt 
ORDER BY a.user_id,b.dt ) tmp1 
GROUP BY 2,3 ;

+---------------+------------+--------+
| users         | dt         | val    |
+---------------+------------+--------+
| 1             | 2021-07-01 |  950.1 |
| 1,2           | 2021-07-02 |  960.1 |
| 2,3,1         | 2021-07-03 |  970.1 |
| 1,2,3,4       | 2021-08-01 |  975.1 |
| 1,2,3,4,5     | 2021-09-01 |  980.1 |
| 1,2,3,4,5,7   | 2021-09-15 |  990.1 |
| 7,1,2,3,4,5,6 | 2021-10-01 | 1100.1 |
| 2,4,5,6,7,1   | 2021-11-01 | 1200.1 |
| 2,4,5,6,7     | 2021-11-15 | 1250.1 |
+---------------+------------+--------+

-- 005 增加序号,为计算做准备

select (@i:=@i+1) id ,tmp2.* from (
select GROUP_CONCAT(tmp1.user_id) users,tmp1.dt, tmp1.val from(
select a.user_id,b.dt,ifnull(c.sv_w,d.ev_w) val
from (select distinct user_id,sdt,IFNULL(edt,(select max(edt) from jghy)) edt from jghy) a 
left join (select distinct sdt dt from jghy
where sdt  is not null 
union 
select distinct edt dt from jghy
where edt is not null ) b  on  b.dt BETWEEN a.sdt and a.edt
left join jghy c on b.dt = c.sdt 
left join jghy d on b.dt = d.edt 
ORDER BY a.user_id,b.dt ) tmp1 
GROUP BY 2,3 ) tmp2 , (select @i:=0) rows ;


+----+---------------+------------+--------+
| id | users         | dt         | val    |
+----+---------------+------------+--------+
|  1 | 1             | 2021-07-01 |  950.1 |
|  2 | 1,2           | 2021-07-02 |  960.1 |
|  3 | 1,3,2         | 2021-07-03 |  970.1 |
|  4 | 2,4,1,3       | 2021-08-01 |  975.1 |
|  5 | 2,4,1,5,3     | 2021-09-01 |  980.1 |
|  6 | 2,4,7,1,5,3   | 2021-09-15 |  990.1 |
|  7 | 6,2,4,7,1,5,3 | 2021-10-01 | 1100.1 |
|  8 | 6,2,4,7,1,5   | 2021-11-01 | 1200.1 |
|  9 | 6,2,4,7,5     | 2021-11-15 | 1250.1 |
+----+---------------+------------+--------+

-- 006 计算用户所在时间节点用水量及整体平均分摊量

select tmp4.id,tmp3.users,tmp4.dt,tmp3.val,(tmp4.val -tmp3.val),(tmp4.val -tmp3.val)/LENGTH(REPLACE(tmp3.users,',','')) val_u  from (
select (@i:=@i+1) id ,tmp2.* from (
select GROUP_CONCAT(tmp1.user_id) users,tmp1.dt, tmp1.val from(
select a.user_id,b.dt,ifnull(c.sv_w,d.ev_w) val
from (select distinct user_id,sdt,IFNULL(edt,(select max(edt) from jghy)) edt from jghy) a 
left join (select distinct sdt dt from jghy
where sdt  is not null 
union 
select distinct edt dt from jghy
where edt is not null ) b  on  b.dt BETWEEN a.sdt and a.edt
left join jghy c on b.dt = c.sdt 
left join jghy d on b.dt = d.edt 
ORDER BY a.user_id,b.dt ) tmp1 
GROUP BY 2,3 ) tmp2 , (select @i:=0) rows ) tmp3 
left join (
select (@o:=@o+1) id ,tmp2.* from (
select GROUP_CONCAT(tmp1.user_id) users,tmp1.dt, tmp1.val from(
select a.user_id,b.dt,ifnull(c.sv_w,d.ev_w) val
from (select distinct user_id,sdt,IFNULL(edt,(select max(edt) from jghy)) edt from jghy) a 
left join (select distinct sdt dt from jghy
where sdt  is not null 
union 
select distinct edt dt from jghy
where edt is not null ) b  on  b.dt BETWEEN a.sdt and a.edt
left join jghy c on b.dt = c.sdt 
left join jghy d on b.dt = d.edt 
ORDER BY a.user_id,b.dt ) tmp1 
GROUP BY 2,3 ) tmp2 , (select @o:=0) rows ) tmp4 on tmp3.id = tmp4.id -1 
where tmp4.id is not null
ORDER BY tmp3.id;

+----+---------------+------------+--------+----------------------+--------------------+
| id | users         | dt         | val    | (tmp4.val -tmp3.val) | val_u              |
+----+---------------+------------+--------+----------------------+--------------------+
|  2 | 1             | 2021-07-02 |  950.1 |                   10 |                 10 |
|  3 | 2,1           | 2021-07-03 |  960.1 |                   10 |                  5 |
|  4 | 3,2,1         | 2021-08-01 |  970.1 |                    5 | 1.6666666666666667 |
|  5 | 3,2,4,1       | 2021-09-01 |  975.1 |                    5 |               1.25 |
|  6 | 1,5,3,2,4     | 2021-09-15 |  980.1 |                   10 |                  2 |
|  7 | 1,5,3,2,4,7   | 2021-10-01 |  990.1 |   109.99999999999989 | 18.333333333333314 |
|  8 | 7,1,5,3,6,2,4 | 2021-11-01 | 1100.1 |                  100 | 14.285714285714286 |
|  9 | 7,1,5,6,2,4   | 2021-11-15 | 1200.1 |                   50 |  8.333333333333334 |
+----+---------------+------------+--------+----------------------+--------------------+

-- 007 每人每时点需要结算的用水量

select substring_index(substring_index(tmp5.users,',',b.help_topic_id+1),',',-1) as user_id ,tmp5.dt,tmp5.val_u  
from  (

select tmp4.id,tmp3.users,tmp4.dt,tmp3.val,(tmp4.val -tmp3.val),(tmp4.val -tmp3.val)/LENGTH(REPLACE(tmp3.users,',','')) val_u  from (
select (@i:=@i+1) id ,tmp2.* from (
select GROUP_CONCAT(tmp1.user_id) users,tmp1.dt, tmp1.val from(
select a.user_id,b.dt,ifnull(c.sv_w,d.ev_w) val
from (select distinct user_id,sdt,IFNULL(edt,(select max(edt) from jghy)) edt from jghy) a 
left join (select distinct sdt dt from jghy
where sdt  is not null 
union 
select distinct edt dt from jghy
where edt is not null ) b  on  b.dt BETWEEN a.sdt and a.edt
left join jghy c on b.dt = c.sdt 
left join jghy d on b.dt = d.edt 
ORDER BY a.user_id,b.dt ) tmp1 
GROUP BY 2,3 ) tmp2 , (select @i:=0) rows ) tmp3 
left join (
select (@o:=@o+1) id ,tmp2.* from (
select GROUP_CONCAT(tmp1.user_id) users,tmp1.dt, tmp1.val from(
select a.user_id,b.dt,ifnull(c.sv_w,d.ev_w) val
from (select distinct user_id,sdt,IFNULL(edt,(select max(edt) from jghy)) edt from jghy) a 
left join (select distinct sdt dt from jghy
where sdt  is not null 
union 
select distinct edt dt from jghy
where edt is not null ) b  on  b.dt BETWEEN a.sdt and a.edt
left join jghy c on b.dt = c.sdt 
left join jghy d on b.dt = d.edt 
ORDER BY a.user_id,b.dt ) tmp1 
GROUP BY 2,3 ) tmp2 , (select @o:=0) rows ) tmp4 on tmp3.id = tmp4.id -1 
where tmp4.id is not null
ORDER BY tmp3.id ) tmp5
join   mysql.help_topic b on b.help_topic_id < (length(tmp5.users) - length(replace(tmp5.users,',',''))+1);

+---------+------------+--------------------+
| user_id | dt         | val_u              |
+---------+------------+--------------------+
| 1       | 2021-07-02 |                 10 |
| 2       | 2021-07-03 |                  5 |
| 1       | 2021-07-03 |                  5 |
| 3       | 2021-08-01 | 1.6666666666666667 |
| 2       | 2021-08-01 | 1.6666666666666667 |
| 1       | 2021-08-01 | 1.6666666666666667 |
| 1       | 2021-09-01 |               1.25 |
| 3       | 2021-09-01 |               1.25 |
| 2       | 2021-09-01 |               1.25 |
| 4       | 2021-09-01 |               1.25 |
| 5       | 2021-09-15 |                  2 |
| 4       | 2021-09-15 |                  2 |
| 1       | 2021-09-15 |                  2 |
| 3       | 2021-09-15 |                  2 |
| 2       | 2021-09-15 |                  2 |
| 1       | 2021-10-01 | 18.333333333333314 |
| 5       | 2021-10-01 | 18.333333333333314 |
| 4       | 2021-10-01 | 18.333333333333314 |
| 7       | 2021-10-01 | 18.333333333333314 |
| 3       | 2021-10-01 | 18.333333333333314 |
| 2       | 2021-10-01 | 18.333333333333314 |
| 4       | 2021-11-01 | 14.285714285714286 |
| 7       | 2021-11-01 | 14.285714285714286 |
| 1       | 2021-11-01 | 14.285714285714286 |
| 6       | 2021-11-01 | 14.285714285714286 |
| 2       | 2021-11-01 | 14.285714285714286 |
| 5       | 2021-11-01 | 14.285714285714286 |
| 3       | 2021-11-01 | 14.285714285714286 |
| 7       | 2021-11-15 |  8.333333333333334 |
| 1       | 2021-11-15 |  8.333333333333334 |
| 2       | 2021-11-15 |  8.333333333333334 |
| 4       | 2021-11-15 |  8.333333333333334 |
| 5       | 2021-11-15 |  8.333333333333334 |
| 6       | 2021-11-15 |  8.333333333333334 |
+---------+------------+--------------------+

-- 008 按租户进行汇总,计算每个租户使用的用水量

select tmp6.user_id,sum(tmp6.val_u) val_u from (

select substring_index(substring_index(tmp5.users,',',b.help_topic_id+1),',',-1) as user_id ,tmp5.dt,tmp5.val_u  
from  (

select tmp4.id,tmp3.users,tmp4.dt,tmp3.val,(tmp4.val -tmp3.val),(tmp4.val -tmp3.val)/LENGTH(REPLACE(tmp3.users,',','')) val_u  from (
select (@i:=@i+1) id ,tmp2.* from (
select GROUP_CONCAT(tmp1.user_id) users,tmp1.dt, tmp1.val from(
select a.user_id,b.dt,ifnull(c.sv_w,d.ev_w) val
from (select distinct user_id,sdt,IFNULL(edt,(select max(edt) from jghy)) edt from jghy) a 
left join (select distinct sdt dt from jghy
where sdt  is not null 
union 
select distinct edt dt from jghy
where edt is not null ) b  on  b.dt BETWEEN a.sdt and a.edt
left join jghy c on b.dt = c.sdt 
left join jghy d on b.dt = d.edt 
ORDER BY a.user_id,b.dt ) tmp1 
GROUP BY 2,3 ) tmp2 , (select @i:=0) rows ) tmp3 
left join (
select (@o:=@o+1) id ,tmp2.* from (
select GROUP_CONCAT(tmp1.user_id) users,tmp1.dt, tmp1.val from(
select a.user_id,b.dt,ifnull(c.sv_w,d.ev_w) val
from (select distinct user_id,sdt,IFNULL(edt,(select max(edt) from jghy)) edt from jghy) a 
left join (select distinct sdt dt from jghy
where sdt  is not null 
union 
select distinct edt dt from jghy
where edt is not null ) b  on  b.dt BETWEEN a.sdt and a.edt
left join jghy c on b.dt = c.sdt 
left join jghy d on b.dt = d.edt 
ORDER BY a.user_id,b.dt ) tmp1 
GROUP BY 2,3 ) tmp2 , (select @o:=0) rows ) tmp4 on tmp3.id = tmp4.id -1 
where tmp4.id is not null
ORDER BY tmp3.id ) tmp5
join   mysql.help_topic b on b.help_topic_id < (length(tmp5.users) - length(replace(tmp5.users,',',''))+1)  ) tmp6 
group by 1;

+---------+--------------------+
| user_id | val_u              |
+---------+--------------------+
| 1       | 60.869047619047606 |
| 2       | 50.869047619047606 |
| 3       |  37.53571428571427 |
| 4       | 44.202380952380935 |
| 5       | 42.952380952380935 |
| 6       |  22.61904761904762 |
| 7       | 40.952380952380935 |
+---------+--------------------+

-- 999 总用水量复核

select sum(val_u) from (

select tmp6.user_id,sum(tmp6.val_u) val_u from (

select substring_index(substring_index(tmp5.users,',',b.help_topic_id+1),',',-1) as user_id ,tmp5.dt,tmp5.val_u  
from  (

select tmp4.id,tmp3.users,tmp4.dt,tmp3.val,(tmp4.val -tmp3.val),(tmp4.val -tmp3.val)/LENGTH(REPLACE(tmp3.users,',','')) val_u  from (
select (@i:=@i+1) id ,tmp2.* from (
select GROUP_CONCAT(tmp1.user_id) users,tmp1.dt, tmp1.val from(
select a.user_id,b.dt,ifnull(c.sv_w,d.ev_w) val
from (select distinct user_id,sdt,IFNULL(edt,(select max(edt) from jghy)) edt from jghy) a 
left join (select distinct sdt dt from jghy
where sdt  is not null 
union 
select distinct edt dt from jghy
where edt is not null ) b  on  b.dt BETWEEN a.sdt and a.edt
left join jghy c on b.dt = c.sdt 
left join jghy d on b.dt = d.edt 
ORDER BY a.user_id,b.dt ) tmp1 
GROUP BY 2,3 ) tmp2 , (select @i:=0) rows ) tmp3 
left join (
select (@o:=@o+1) id ,tmp2.* from (
select GROUP_CONCAT(tmp1.user_id) users,tmp1.dt, tmp1.val from(
select a.user_id,b.dt,ifnull(c.sv_w,d.ev_w) val
from (select distinct user_id,sdt,IFNULL(edt,(select max(edt) from jghy)) edt from jghy) a 
left join (select distinct sdt dt from jghy
where sdt  is not null 
union 
select distinct edt dt from jghy
where edt is not null ) b  on  b.dt BETWEEN a.sdt and a.edt
left join jghy c on b.dt = c.sdt 
left join jghy d on b.dt = d.edt 
ORDER BY a.user_id,b.dt ) tmp1 
GROUP BY 2,3 ) tmp2 , (select @o:=0) rows ) tmp4 on tmp3.id = tmp4.id -1 
where tmp4.id is not null
ORDER BY tmp3.id ) tmp5
join   mysql.help_topic b on b.help_topic_id < (length(tmp5.users) - length(replace(tmp5.users,',',''))+1)  ) tmp6 
group by 1
) tmp7;

+-------------------+
| sum(val_u)        |
+-------------------+
| 299.9999999999999 |
+-------------------+


select min(sv_w),max(ev_w), max(ev_w)- min(sv_w) from jghy;
+-----------+-----------+----------------------+
| min(sv_w) | max(ev_w) | max(ev_w)- min(sv_w) |
+-----------+-----------+----------------------+
|     950.1 |    1250.1 |    299.9999999999999 |
+-----------+-----------+----------------------+