一.需求背景 | KWAN的解忧杂货铺

# 一.需求背景

# 1.如何动态展示红包排名

现在有个需,需要动态展示近一个月 csdn 用户抢红包的动态排名,如何实现呢?

获取用户每一天的累计红包
使用 pyecharts 对数据进行绘图
通过视频动态展示排名

# 2.实现步骤

数据准备,拉取每个人领取红包的情况
对 1 的数据进行处理,得到每一天每人的累计数据
每天只取金额前十的人,因为如果太多了也没有意义
补全每一天的数据,让每一天的人是相同的,为了累计计算
使用 sum over 计算累计所得

# 二.数据准备

# 1.CTE 表达式

简单例子

WITH RECURSIVE EmployeeCTE AS (
    SELECT employee_id, employee_name, manager_id
    FROM employees
    WHERE manager_id IS NULL

    UNION ALL

    SELECT e.employee_id, e.employee_name, e.manager_id
    FROM employees e
    JOIN EmployeeCTE ec ON e.manager_id = ec.employee_id
)
SELECT * FROM EmployeeCTE;

1
2
3
4
5
6
7
8
9
10
11
12

# 2.基础数据

一直 csdn_red_package_detail_info 保存了每个红包没人抢的红包信息

SELECT receiver_nick_name                    AS receiver_nick_name
     , DATE_FORMAT(receive_time, '%Y-%m-%d') AS receive_time
     , received_money                        AS received_money
FROM csdn_red_package_detail_info
;

1
2
3
4
5

# 3.每人每天

获取每人每天获取的总金额

 SELECT receiver_nick_name  AS receiver_nick_name
         , receive_time        AS receive_time
         , SUM(received_money) AS received_money
    FROM man_day_money
    GROUP BY receiver_nick_name, receive_time

1
2
3
4
5

# 4.检查数据是否重复

SELECT receiver_nick_name, receive_time, received_money
FROM man_day_money_sum
GROUP BY  receiver_nick_name, receive_time, received_money
HAVING COUNT(*) > 1;

1
2
3
4

# 5.每天按照金额排名

因为每天领取的人太多了需要对领取人进行排名

SELECT receiver_nick_name
         , receive_time
         , received_money
         , ROW_NUMBER() OVER (PARTITION BY receive_time ORDER BY received_money DESC) AS rank_num
    FROM man_day_money_sum

1
2
3
4
5

# 6.只取前 10 名

SELECT receiver_nick_name
         , receive_time
         , received_money
    FROM man_day_money_rank
    WHERE rank_num <= 10

1
2
3
4
5

# 7.建表

创建人-天-金额表

CREATE TABLE `csdn_red_package_total_rank_info`
(
    `id`                 int UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键id',
    `receiver_nick_name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '用户名',
    `receive_time`       date         NULL                                            DEFAULT NULL COMMENT '领取时间',
    `received_money`     decimal(10, 2)                                               DEFAULT '0.00' COMMENT '金额',
    `create_time`        timestamp    NULL                                            DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    `update_time`        timestamp    NULL                                            DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    `is_delete`          tinyint(1)   NOT NULL                                        DEFAULT '0' COMMENT '逻辑删除,0未删除,1已删除',
    PRIMARY KEY (`id`) USING BTREE,
    KEY `csdn_red_package_order_no_IDX` (`receiver_nick_name`, `receive_time`) USING BTREE
) ENGINE = InnoDB
  AUTO_INCREMENT = 1
  DEFAULT CHARSET = utf8mb4
  COLLATE = utf8mb4_0900_ai_ci
  ROW_FORMAT = DYNAMIC COMMENT ='csdn红包每天用户领取详细信息'
;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

将查询的数据插入到创建的表中csdn_red_package_total_rank_info

WITH man_day_money AS (
    SELECT receiver_nick_name                    AS receiver_nick_name
         , DATE_FORMAT(receive_time, '%Y-%m-%d') AS receive_time
         , received_money                        AS received_money
    FROM csdn_red_package_detail_info
)
   , man_day_money_sum AS (
    SELECT receiver_nick_name  AS receiver_nick_name
         , receive_time        AS receive_time
         , SUM(received_money) AS received_money
    FROM man_day_money
    GROUP BY receiver_nick_name, receive_time
)
   , man_day_money_rank AS (
    SELECT receiver_nick_name
         , receive_time
         , received_money
         , ROW_NUMBER() OVER (PARTITION BY receive_time ORDER BY received_money DESC) AS rank_num
    FROM man_day_money_sum
)

   , man_day_money_limit AS (
    SELECT receiver_nick_name
         , receive_time
         , received_money
    FROM man_day_money_rank
    WHERE rank_num <= 10
)

SELECT *
FROM man_day_money_limit
ORDER BY receive_time DESC, received_money DESC
;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33

# 8.Java 补数据

因为每一天的人是不要的,要补全数据,让每一天的人是相同的,这样才能算每一天没人的累计

@Override
public void complete() {
    QueryWrapper<CsdnRedPackageTotalRankInfo> wrapper = new QueryWrapper<>();
    wrapper.eq("is_delete", 0);
    final List<CsdnRedPackageTotalRankInfo> list = this.list(wrapper);
    if (CollectionUtil.isNotEmpty(list)) {
        final Set<String> users = list.stream().map(CsdnRedPackageTotalRankInfo::getReceiverNickName).collect(Collectors.toSet());
        final List<String> days = this.days();
        if (CollectionUtil.isNotEmpty(days)) {
            for (String day : days) {
                QueryWrapper<CsdnRedPackageTotalRankInfo> wrapper_day = new QueryWrapper<>();
                wrapper_day.eq("is_delete", 0);
                wrapper_day.eq("receive_time", day);
                final List<CsdnRedPackageTotalRankInfo> dayList = this.list(wrapper_day);
                if (CollectionUtil.isNotEmpty(dayList)) {
                    final Set<String> dayUsers = dayList.stream().map(CsdnRedPackageTotalRankInfo::getReceiverNickName).collect(Collectors.toSet());
                    for (String user : users) {
                        if (!dayUsers.contains(user)) {
                            CsdnRedPackageTotalRankInfo info = new CsdnRedPackageTotalRankInfo();
                            info.setReceiverNickName(user);
                            info.setReceivedMoney(BigDecimal.ZERO);
                            SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
                            try {
                                Date date = dateFormat.parse(day);
                                info.setReceiveTime(date);
                                this.save(info);
                            } catch (Exception e) {
                                e.printStackTrace();
                            }
                        }
                    }
                }
            }
        }
    }
}

/**
 * 获取日期集合
 *
 * @return
 */
private List<String> days() {
    List<String> dates = new ArrayList<>();
    final DateTime tomorrow = DateUtil.tomorrow();
    for (int i = 1; i <= 35; i++) {
        final DateTime dateTime = DateUtil.offsetDay(tomorrow, -i);
        dates.add(DateUtil.formatDate(dateTime));
    }
    return dates;
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51

# 9.累计数据

根据人分组,求每一天每个人的历史累计值,导出数据到 csv

SELECT receiver_nick_name
     , receive_time
     , received_money
     , SUM(received_money) OVER (PARTITION BY receiver_nick_name ORDER BY receive_time) AS total_received_money
FROM csdn_red_package_total_rank_info
ORDER BY receive_time DESC, total_received_money DESC;

1
2
3
4
5
6

# 三.图形化展示

# 1.python 代码

from pyecharts.charts import Bar, Timeline
from pyecharts.options import *
from pyecharts.globals import ThemeType
from pyecharts import options as opts

# 读取数据
f = open("/Users/qinyingjie/Documents/python-workspace/python-demo/data/动态柱状图数据/csdn.csv", "r",
         encoding="UTF-8")
data_lines = f.readlines()
# 关闭文件
f.close()
# 删除第一条数据
data_lines.pop(0)
# 先定义一个字典对象
data_dict = {}
for line in data_lines:
    year = (line.split(",")[0])  # 日期
    country = line.split(",")[1]  # 昵称
    gdp = float(line.split(",")[2])  # 红包数据
    # 如何判断字典里面有没有指定的key呢？
    try:
        data_dict[year].append([country, gdp])
    except KeyError:
        data_dict[year] = []
        data_dict[year].append([country, gdp])

# print(data_dict[1960])
# 创建时间线对象
timeline = Timeline({"theme": ThemeType.LIGHT})
# 排序年份
sorted_year_list = sorted(data_dict.keys())
for year in sorted_year_list:
    data_dict[year].sort(key=lambda element: element[1], reverse=True)
    # 取出本年份前8名的国家
    year_data = data_dict[year][0:8]
    x_data = []
    y_data = []
    for i, country_gdp in enumerate(year_data):
        x_data.append(country_gdp[0])  # x轴添加国家
        y_data.append(country_gdp[1])  # y轴添加gdp数据

    # 构建柱状图
    bar = Bar()
    x_data.reverse()
    y_data.reverse()

    bar.add_xaxis(x_data)
    bar.add_yaxis("红包数据", y_data, label_opts=LabelOpts(position="right"))
    # 反转x轴和y轴
    bar.reversal_axis()
    # 设置每一天的图表的标题
    bar.set_global_opts(
        title_opts=TitleOpts(title=f"{year}-CSDN抢到红包累计排名")
    )
    timeline.add(bar, str(year))

# 设置时间线自动播放
timeline.add_schema(
    play_interval=2000,
    is_timeline_show=True,
    is_auto_play=True,
    is_loop_play=False
)
# 绘图
timeline.render("2023年11月11-2023年12月15每天CSDN抢到红包累计排名前8用户.html")

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65

# 2.效果图

# 3.视频效果

B 站链接 (opens new window)

← 02-Python工程化 04-Python进阶 →