SQL进阶（六）：通关题：制作一个活动日历

最新推荐文章于 2024-10-31 15:21:46 发布
最新推荐文章于 2024-10-31 15:21:46 发布
阅读量698 收藏 26
点赞数 20
分类专栏： 数据库 文章标签： sql 数据库 sqlite
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qq_33489955/article/details/138927756
版权

目录


本文是在原本sql闯关的基础上总结得来，加入了自己的理解以及疑问解答（by GPT4）

原活动链接

题目6-10参考大佬：云逸天沧丶
大佬的解题角度

😈 欢迎来到最后一关！

上一关中，我们简单介绍了通过 WITH 函数创建 CTE 和 VIEW 构建视图的方式来提升代码的可阅读性和可维护性，而最后的闯关题希望你能够好好利用他们。

运行下面的代码，准备开始应对挑战吧！

# 改为 False 可以跳过下面的动画
watch_anime = True

import itertools
import threading
import time
import sys

done = False
#here is the animation
def animate():
    dots = 1
    for c in itertools.cycle(['|', '/', '-', '\\']):
        if done:
            break
        dots+=1
        if dots >=30 and dots <150:
            sys.stdout.write('\r打磨武器中... '+'.'*(dots//9) + c)
            sys.stdout.flush()
            time.sleep(0.02)
            continue
        if dots >=150 and dots<330:
            sys.stdout.write('\r准备药水ing... '+'.'*(dots//9) + c)
            sys.stdout.flush()
            continue
        if dots >=330 and dots <=560:
            sys.stdout.write('\r正在热身... '+'.'*(dots//9) + c)
            sys.stdout.flush()
            time.sleep(0.02)

            continue

        if dots >560 and dots <=760:
            sys.stdout.write('\r打开传送门... '+'.'*(dots//9) + c)
            sys.stdout.flush()
            time.sleep(0.02)

            continue

        sys.stdout.write('\rloading '+'.'*(dots//9) + c)
        sys.stdout.flush()
        time.sleep(0.02)
    sys.stdout.flush()
    dots =0 
    print('\rDone!     ')



if watch_anime:
    t = threading.Thread(target=animate)
    t.start()
    #long process here
    time.sleep(10)
    done = True

Done!     ......................................................................./

​

通关题：用 SQL 制作一个活动日历

from IPython.display import Markdown as md

text = [
    '### 😈：欢迎来到**最后**的闯关题',
    '### 😈: 通过了这一关，就代表你完成了成为 SQL 高手的试炼 🔥',
    '### 😈: 在此前的教程当中，你都借助了和鲸社区上的真实数据集来帮助你联系',
    '### 😈: 然而在这里，你却要面对没有真实数据集的挑战了（发出邪恶的笑声）',
    '### 😈: 那么就让我来告诉你你将面对的是什么吧！',
    
]
tid = 0
if watch_anime:
    for i in range(5):
        display(md(text[i]))
        time.sleep(1.5)
else:
     display(md('\n\n\n '.join(text)))

😈：欢迎来到最后的闯关题

😈: 通过了这一关，就代表你完成了成为 SQL 高手的试炼 🔥

😈: 在此前的教程当中，你都借助了和鲸社区上的真实数据集来帮助你联系

😈: 然而在这里，你却要面对没有真实数据集的挑战了（发出邪恶的笑声）

😈: 那么就让我来告诉你你将面对的是什么吧！

任务 1： 制作一个日历

😈: 作为一个心思缜密的恶魔，我需要准确的知道每天的日期，来制定我的邪恶计划（发出更加邪恶的笑声）。但是我的手边却没有公元 2023 年的日历！在已知 2023 年 1月 1日是周日的前提下：

😈: 请通过你学会的 SQL 知识，为我制作出 2023 年一年的日历

😈: 我需要如下几个信息：

  1. 每天的日期，用 年份-月份-日期 表示（例如 2023-01-01）

  2. 每天是星期几（用数字表示，1 代表星期一，以此类推）

  3. 当前是今年的第几周 （用数字表示，1 代表第一周，以此类推）

  4. 当前是日期是否是本月的最后一个星期 X （例如，2023-07-25 就是本月最后一个星期 2，是的话返回 True，否则返回 False）

text = [
    '### 🐳：不要害怕！正义的伙伴小鲸来帮忙了<span style="color:blue">（正义的 bgm 响起）</span>',
    '### 🐳：虽然闯关题看起来很可怕，但是不用担心，我这里为你准备了一些装备',
    '### 🐳：还有一些可行的攻略 HINTS ，帮助你理清思路', 
]

if watch_anime:
    for i in range(3):
        display(md(text[i]))
        time.sleep(1.5)
else:
    display(md('\n\n\n'.join(text)))

🐳：不要害怕！正义的伙伴小鲸来帮忙了（正义的 bgm 响起）

🐳：虽然闯关题看起来很可怕，但是不用担心，我这里为你准备了一些装备

🐳：还有一些可行的攻略 HINTS ，帮助你理清思路

🐳：首先，这里是一个从 0 到 364 的数列，你可以用它去制作最基本的日期

import pandas as pd
year_range = pd.DataFrame({"index":range(365)})
year_range.head()
index
00
11
22
33
44
# import calendar
# from datetime import datetime, timedelta
# import pandas as pd

# def create_calendar_with_monday_start(year):
#     # Create a calendar with Monday as the first day of the week
#     cal = calendar.Calendar(firstweekday=0)  # Set Sunday as the first day for iteration
#     calendar_data = []

#     for month in range(1, 13):
#         # Use itermonthdates to include days from the adjacent months
#         month_days = list(cal.itermonthdates(year, month))
#         # Filter out days not in the current month
#         filtered_month_days = [day for day in month_days if day.month == month]
        
#         for day in filtered_month_days:
#             # Adjust the weekday to start from Monday (1) to Sunday (7)
#             week_day = day.weekday() + 1
#             # Calculate the week number with Monday as the first day of the week
#             if day.weekday() == 6:  # Sunday
#                 # If it's Sunday, treat it as the end of the week
#                 week_number = int(day.strftime('%W')) + 1
#             else:
#                 # For other days, shift to start the week on Monday
#                 week_number = int((day - timedelta(days=week_day - 1)).strftime('%W')) + 1

#             # Check if this day is the last occurrence of this weekday in the month
#             is_last_week_x = ((day + timedelta(days=7)).month != month)

#             calendar_data.append({
#                 "date": day.strftime('%Y-%m-%d'),
#                 "week_day": week_day,
#                 "week_number": week_number,
#                 "is_last_week_x": is_last_week_x
#             })

#     return calendar_data

# # Generate the calendar for 2023 with adjusted week starts
# adjusted_calendar_2023 = create_calendar_with_monday_start(2023)
# adjusted_calendar_df = pd.DataFrame(adjusted_calendar_2023)
# adjusted_calendar_df.head(10)  # Display the first 10 rows to check
dateweek_dayweek_numberis_last_week_x
02023-01-0171False
12023-01-0212False
22023-01-0322False
32023-01-0432False
42023-01-0542False
52023-01-0652False
62023-01-0762False
72023-01-0872False
82023-01-0913False
92023-01-1023False
# 更方便的创建方式
import pandas as pd
from math import ceil
import sqlite3
df = pd.DataFrame({'datetime':pd.date_range('2023-01-01','2023-12-31')})
df['date'] = df['datetime'].dt.date
df['year'] = df['datetime'].dt.year
df['month'] = df['datetime'].dt.month
df['day'] = df['datetime'].dt.day
df['week_day'] = df['datetime'].dt.weekday
df['day_of_year'] = df['datetime'].dt.dayofyear
df['week_number'] = df['datetime'].dt.isocalendar().week
#调整weekday
df['week_day'] = df['week_day'] + 1
#调整week
df['week_number'][0] = 0
df['week_number'] = df['week_number'] + 1
#获取当月的第几周
def week_of_month(dt, start_date=0):
  first_day = dt.replace(day=1)  # 本月第一天
  adjusted_dom = dt.day + (first_day.weekday() + start_date) % 7
  return int(ceil(adjusted_dom / 7.0))
#写入DataFrame
WOM = []
for i in range(len(df)):
  WOM.append(week_of_month(df.loc[i,'datetime']))
df['week_of_month'] = WOM
df.head()
datetimedateyearmonthdayweek_dayday_of_yearweek_numberweek_of_month
02023-01-012023-01-012023117111
12023-01-022023-01-022023121222
22023-01-032023-01-032023132322
32023-01-042023-01-042023143422
42023-01-052023-01-052023154522
df.dtypes

datetime         datetime64[ns]
date                     object
year                      int64
month                     int64
day                       int64
week_day                  int64
day_of_year               int64
week_number              UInt32
week_of_month             int64
dtype: object

# adjusted_calendar_df.to_excel('demo.xlsx',index = False)

# 导入库
from IPython.display import Markdown as md
from matplotlib import pyplot as plt
import sqlite3
import pandas as pd
import numpy as np
import json
from copy import deepcopy
from datetime import datetime

plt.rcParams['font.sans-serif'] = ['SimHei']
plt.rcParams['axes.unicode_minus'] = False

# 将数据写入一张名为 calendar_table 的表
with sqlite3.connect('calendar_table.db') as conn:
    df.to_sql('calendar_table',con = conn,if_exists='replace',index = False)
    
connection = sqlite3.connect('calendar_table.db')

提问：conn 以及 connection 都是在建立游标以便连接数据库吗

回答：在Python中使用 sqlite3 库与SQLite数据库交互时，conn 和 connection 都是数据库连接对象。它们不是游标，而是用来建立和管理与数据库的连接。你可以通过这些连接对象来创建游标对象，游标则用于执行SQL命令和管理交互过程。

这里是一个简单的示例说明如何使用连接对象创建游标，并通过游标执行SQL命令：

import sqlite3

# 建立数据库连接
conn = sqlite3.connect('example.db')

# 使用连接对象创建游标
cursor = conn.cursor()

# 通过游标执行SQL命令
cursor.execute("CREATE TABLE IF NOT EXISTS test (id INTEGER PRIMARY KEY, name TEXT)")
cursor.execute("INSERT INTO test (name) VALUES ('Alice')")

# 提交事务
conn.commit()

# 关闭游标
cursor.close()

# 关闭连接
conn.close()

在这个例子中，conn 是一个连接对象，用于管理数据库的连接。cursor 是通过 conn.cursor() 创建的游标对象，用于执行SQL命令。在执行完命令后，使用 conn.commit() 来提交事务，确保数据被写入数据库。最后，使用 cursor.close() 和 conn.close() 关闭游标和连接，释放资源。

提问：类似这样的的语法似乎并没有按照上述方式创建游标：connection.execute(query).fetchall()

回答：确实，您提到的 connection.execute(query).fetchall() 这种语法实际上是一个快捷方式，它在没有显式创建游标对象的情况下执行了SQL查询。

在Python的 sqlite3 库中，当你使用 connection.execute() 方法时，它内部自动创建了一个临时的游标对象，执行了SQL语句，并立即返回结果。这种方法对于执行单个查询非常方便，因为它省去了手动创建和管理游标对象的步骤。

这是一个具体的例子，展示了如何使用这种快捷方法执行查询并获取结果：

import sqlite3

# 建立数据库连接
connection = sqlite3.connect('example.db')

# 直接使用连接执行查询并获取所有结果
query = "SELECT * FROM test"
results = connection.execute(query).fetchall()

# 打印查询结果
for row in results:
    print(row)

# 关闭连接
connection.close()

在这个例子中，通过 connection.execute(query) 直接执行SQL命令，并通过 fetchall() 获取所有行的数据。这种方法简化了代码，适用于执行简单的SQL操作，但如果需要进行更复杂的事务管理或是多次使用同一个游标执行多条语句，通常还是建议显式创建游标。

🐳：其次，我们在教程当中所以使用的 SQLite 可以支持对于一个修改某个具体的日期的，例如 DATE(‘2023-01-01’,‘+1 days’) 参考这里的文档，这个文档里面有很多隐藏秘籍，仔细阅读和消化会让你功力大增哦

🐳：一些 HINTS：

  1. 可以从已知的，固定的日期出发，首先搭起完整的日期表

  2. “周”是一个具有周期性概念的数据字段，记住这一点

  3. 一个月的第一周可以是另一个月的最后一周

dateweek_day week_number is_last_week_x
02023-01-01 7 1 False
12023-01-02 1 2 False
22023-01-03 2 2 False

😈: 既然你获得了外界的帮助，那么我需要在结果要求你：

Q1: 在 2023 年当中，星期 2 出现的次数和星期 5 出现的次数的关系是？（选择 > 或 < 或 =）
query = '''
SELECT
    week_day,
    count(*)
FROM calendar_table
GROUP BY week_day
'''
result = connection.execute(query).fetchall()
result

[(1, 52), (2, 52), (3, 52), (4, 52), (5, 52), (6, 52), (7, 53)]

# ...your code...
a1 = '='  # 在 '' 中填入你的结果，如 a1 = '>' 代表星期 2 出现的次数大于星期 5 出现的次数
Q2: 在 2023 年每个月的5号，10号，15号，20号和 25号 中，星期几的占比最高
query = '''
SELECT
    week_day,
    count(*)
FROM calendar_table
WHERE 
    strftime('%d',date) IN ('05','10','15','20','25')
GROUP BY
    week_day
'''
result = connection.execute(query).fetchall()
result

[(1, 9), (2, 8), (3, 10), (4, 7), (5, 9), (6, 8), (7, 9)]

# ...your code...
a2 = '3' # 在 '' 中填入你的结果，如 a2 = '2' 代表星期 2 的占比最高
Q3: 在 2023 年第34周当中，最大的日期是几号？
query = '''
SELECT
    MAX(date)
FROM
    calendar_table
WHERE
    week_number = '34'
'''
result = connection.execute(query).fetchall()
result

[('2023-08-20',)]

# ...your code...
a3 = '20'  # 在 '' 中填入你的结果，如 a3 = '23' 代表当周日期最大的是 23 号
Q4: 在 2023 年所有月份的最后 1 周，一共有多少个周末

就是求当月的最后一天是周几，周六算1天，周日算两天

query = '''
With MonthlyLastDays AS(
    SELECT
        strftime('%Y-%m',date) AS year_month,
        MAX(date) AS last_day,
        (CASE
            WHEN week_day = '6' THEN 1
            WHEN week_day = '7' THEN 2
            ELSE 0
        END) AS week_day_of_value
    FROM
        calendar_table
    GROUP BY
        year_month
)
SELECT
    SUM(week_day_of_value) as weekend_of_month_tail
FROM 
    MonthlyLastDays
'''
result = connection.execute(query).fetchall()
result

[(5,)]

# ...your code...
a4 = '5'  # 在 '' 中填入你的结果，如 a4 = '5' 代表有个 5 个周末（周六，周日分开计算，例如 2个周六 和 3个周日 = 5个周末）
Q5: 在 2023 年所有月份的 5 号，最长一次连续处在当月同一周是连续几个月？

例如：2,3,4,5月份的 5 号都处于当月的第 2 周， 则最长一次为 4

# 提取2023年每个月5号的date、week_day和week_number
query = '''
SELECT
    date,
    week_of_month
FROM
    calendar_table
WHERE
    strftime('%Y-%m-%d',date) LIKE '2023-__-05'
'''
result = connection.execute(query).fetchall()
result

[('2023-01-05', 2),
 ('2023-02-05', 1),
 ('2023-03-05', 1),
 ('2023-04-05', 2),
 ('2023-05-05', 1),
 ('2023-06-05', 2),
 ('2023-07-05', 2),
 ('2023-08-05', 1),
 ('2023-09-05', 2),
 ('2023-10-05', 2),
 ('2023-11-05', 1),
 ('2023-12-05', 2)]

# 分析连续相同week_of_month的最长长度
max_length = 0
current_length = 1
for i in range(1, len(result)):
    if result[i][1] == result[i-1][1]:
        current_length += 1
        max_length = max(max_length, current_length)
    else:
        current_length = 1  # 重置连续长度

max_length = max(max_length, current_length)  # 最后一次比较后更新最大长度

print("最长连续处在当月同一周的月份数是:", max_length)

最长连续处在当月同一周的月份数是: 2

​

# ...your code...
a5 = '2'  # 在 '' 中填入你的结果，如 a5 = '5' 代表可以最长一次是 5 个月连续

任务 2： 制作活动日历

😈: 现在我手上拿到了你的日历了，你是一个值得尊敬的对手，但是我需要将我的邪恶计划安排在日历上，这样我才能所向披靡！

😈: 现在我的手上有一份邪恶 TO-DO 清单，里面有如下的字段：

event: 我的计划名称

start: 邪恶事项开始的日期

duration: 邪恶事项持续的天数（包括开始的日期）

importance:邪恶事项的重要性

# 😈: 我的邪恶 TO-DO 清单
events = [
    '打磨新的兵器',
     '看1月新番',
    '修补被破坏的城堡',
    '安排小恶魔去收保护费',
    '冶炼最强的金属',
    '召集邪恶手下开会',
    '搜集有关英雄的情报',
    '设计恶魔标志',
    '看4月新番',
    '召唤恶魔仪式',
    '策划一场恶作剧',
    '看7月新番',
    '编写邪恶手册',
    '破坏隔壁恶魔的商业计划',
    '招募新的邪恶成员',
    '开展黑暗仪式',
    '夺取宝藏',
    '收集魔法物品',
    '引发恐慌和混乱',
    '发展邪恶科技',
    '看10月新番',
    '制造毒药和诅咒',
    '建立邪恶王国',
    '征服世界！',
    '破坏英雄的声誉',
    '召唤邪恶精灵军团',
    '设计毁灭性的邪恶装置',
    '复活强大的邪恶巫师',
    '制造无法抵挡的瘟疫',
    '建立邪恶的秘密基地',
    '发动黑暗能量的仪式',
    '统治邪恶的次元',
    '煽动反抗军',
    '引发邪恶的大洪水',
    '驱使野兽攻击无辜的人们',
    '发起致命的黑市交易',
    '堕落正义的守护者',
    '启动毁灭性的计时器',
    '发展邪恶的生化科技',
    '洗脑城市居民',
    '释放黑暗之魂',
    '摧毁圣地和神殿',
    '追捕并抹杀英雄',
]
start_date = [
    '2023-02-10',
    '2023-05-02',
    '2023-02-23',
    '2023-03-17',
    '2023-04-29',
    '2023-09-05',
    '2023-05-12',
    '2023-03-22',
    '2023-04-09',
    '2023-07-13',
    '2023-09-10',
    '2023-07-05',
    '2023-08-31',
    '2023-10-08',
    '2023-08-28',
    '2023-09-01',
    '2023-09-07',
    '2023-08-22',
    '2023-11-18',
    '2023-12-11',
    '2023-10-12',
    '2023-01-03',
    '2023-06-21',
    '2023-12-28',    
    '2023-08-18',
    '2023-06-11',
    '2023-12-04',
    '2023-04-16',
    '2023-11-09',
    '2023-07-26',
    '2023-05-21',
    '2023-12-31',
    '2023-01-09',
    '2023-03-29',
    '2023-07-19',
    '2023-03-22',
    '2023-10-04',
    '2023-08-14',
    '2023-11-30',
    '2023-04-19',
    '2023-02-11',
    '2023-01-02',
    '2023-09-06',

]
duration = [
    17, 8, 31, 14, 28, 20, 24, 5, 9, 26
    , 12, 21, 15, 30, 11, 4, 25, 5,
    18, 16, 10, 23, 29, 6, 27, 13, 22, 6,
    32, 7, 25, 6, 12, 29, 13, 22, 9, 14,
    21, 8, 30, 4, 19
]
importance = [
    2, 3, 1, 0, 1, 0, 1, 2, 3, 2,
    2, 3, 0, 0, 3, 1, 3, 1, 3, 2,
    3, 0, 1, 2, 1, 3, 2, 1, 0, 0,
    1, 1, 3, 0, 2, 2, 0, 3, 2, 3,
    3, 2, 0
]
evil = pd.DataFrame({'event':events,'start_date':start_date,'duration':duration,'importance':importance})
evil
eventstart_datedurationimportance
0打磨新的兵器2023-02-10172
1看1月新番2023-05-0283
2修补被破坏的城堡2023-02-23311
3安排小恶魔去收保护费2023-03-17140
4冶炼最强的金属2023-04-29281
5召集邪恶手下开会2023-09-05200
6搜集有关英雄的情报2023-05-12241
7设计恶魔标志2023-03-2252
8看4月新番2023-04-0993
9召唤恶魔仪式2023-07-13262
10策划一场恶作剧2023-09-10122
11看7月新番2023-07-05213
12编写邪恶手册2023-08-31150
13破坏隔壁恶魔的商业计划2023-10-08300
14招募新的邪恶成员2023-08-28113
15开展黑暗仪式2023-09-0141
16夺取宝藏2023-09-07253
17收集魔法物品2023-08-2251
18引发恐慌和混乱2023-11-18183
19发展邪恶科技2023-12-11162
20看10月新番2023-10-12103
21制造毒药和诅咒2023-01-03230
22建立邪恶王国2023-06-21291
23征服世界！2023-12-2862
24破坏英雄的声誉2023-08-18271
25召唤邪恶精灵军团2023-06-11133
26设计毁灭性的邪恶装置2023-12-04222
27复活强大的邪恶巫师2023-04-1661
28制造无法抵挡的瘟疫2023-11-09320
29建立邪恶的秘密基地2023-07-2670
30发动黑暗能量的仪式2023-05-21251
31统治邪恶的次元2023-12-3161
32煽动反抗军2023-01-09123
33引发邪恶的大洪水2023-03-29290
34驱使野兽攻击无辜的人们2023-07-19132
35发起致命的黑市交易2023-03-22222
36堕落正义的守护者2023-10-0490
37启动毁灭性的计时器2023-08-14143
38发展邪恶的生化科技2023-11-30212
39洗脑城市居民2023-04-1983
40释放黑暗之魂2023-02-11303
41摧毁圣地和神殿2023-01-0242
42追捕并抹杀英雄2023-09-06190
evil.dtypes

event         object
start_date    object
duration       int64
importance     int64
dtype: object

TODO清单表

直接学习套用社区大佬的结题方法

1.先根据start_date和duration生成对应的end_date；

2.然后将整个表转化成【日期，事件，分数】的排列形式；

3.新增了一列状态列，展示该任务在该日期下是 开始/进行/结束。

#生成end_date
evil['start_date'] = pd.to_datetime(evil['start_date']) # 将 evil DataFrame中的 start_date 列从字符串或其他格式转换为Pandas的日期时间格式
evil['end_date'] = evil['start_date'] + pd.to_timedelta(evil['duration'], unit='d') # pd.to_timedelta 用于将持续天数转换为时间差，以便加到起始日期上。
#转化表格形式
evil1 = pd.DataFrame({'date':[],'event':[],'importance':[]}) # 初始化了一个新的DataFrame evil1，其中包含空的 date、event 和 importance 列
for i in range(len(evil)):
    df2 = pd.DataFrame({
        'date':pd.date_range(evil.loc[i,'start_date'],evil.loc[i,'end_date']),
        'event':evil.loc[i,'event'],
        'importance':evil.loc[i,'importance']
    })
    '''
    上述代码遍历 evil DataFrame中的每一行（每个事件），并为每个事件创建一个新的DataFrame df2。
    df2 中的 date 列是从该事件的起始日期到结束日期的完整日期范围。
    event 和 importance 列分别用该事件的名称和重要性填充。
    '''
    # 为每一天标记描述
    for x in range(len(df2)):
        if df2.loc[x,'date'] == evil.loc[i,'start_date']:
            desc = '开始'
        elif df2.loc[x,'date'] == evil.loc[i,'end_date']:
            desc = '结束'
        else:
            desc = '进行'
        df2.loc[x,'desc'] = desc
    evil1 = pd.concat([evil1,df2]) # 合并到主DataFrame
# 调整日期和数据类型格式
evil1['date'] = pd.to_datetime(evil1['date']).dt.date # 从字符串或其他格式转换为Pandas的日期时间格式后再转化为日期格式
evil1['importance'] = evil1['importance'].astype('int')

evil1.head()
dateeventimportancedesc
02023-02-10打磨新的兵器2开始
12023-02-11打磨新的兵器2进行
22023-02-12打磨新的兵器2进行
32023-02-13打磨新的兵器2进行
42023-02-14打磨新的兵器2进行
evil1.dtypes

date          object
event         object
importance     int32
desc          object
dtype: object

# 将数据写入一张名为 evil_table 的表
with sqlite3.connect('calendar_table.db') as conn:
    evil1.to_sql('evil_table',con = conn,if_exists='replace',index = False)
    
connection = sqlite3.connect('calendar_table.db')

😈: 然后你需要回答以下的几个问题：

Q6: 在 2023 年当中，我有几天是可以休假的（没有任何一个邪恶事项正在进行）?

思路：calendar_table(简称t1) 关联 evil_table(简称t2)，关联时t2表需要舍弃状态为“结束”的行(任务是在结束标志的前一天结束)。关联后筛选t2的date为null的行计数，即可得出答案

query = '''
SELECT
    count(t1.date)
FROM
    calendar_table AS t1
LEFT JOIN(
    SELECT DISTINCT date FROM evil_table WHERE desc !='结束'
)AS t2
ON t1.date = t2.date
WHERE t2.date is null
'''
result = connection.execute(query).fetchall()
result

[(29,)]

# ...your code...
a6 = '29'  # 在 '' 中填入你的结果，如 a6 = '5' 代表我有 5 天可以休假
Q7: 在 2023 年当中，我最多一天要执行几个邪恶事项？

思路1：以日期分组得到每天的事件数量（注意舍弃状态为“结束”的行），再得到最大的事件数量

query = '''
SELECT
    MAX(event_nums)
FROM
    (SELECT COUNT(event) as event_nums FROM evil_table WHERE desc != '结束' GROUP BY date) as t2
'''
result = connection.execute(query).fetchall()
result

[(6,)]

# ...your code...
a7 = '6'  # 在 '' 中填入你的结果，如 a7 = '5' 代表有一天需要执行 5 个事项
Q8: 在 2023 年当中，我一天需要忙碌多少天？

忙碌的定义：1 天有 >= 2个事项正在执行 或 未来 3天内有 1 个重要性 >1 的事项要结束 或 未来 3 天内有 1 个重要性 > 2 的事项要开始

关于“未来 N 天” 的定义：假设一个满足条件的事件在 2023-01-06 结束/开始，那么它在 2023-01-03 的 未来 3 天之内，但是不在 2023-01-06 的“未来 3 天之内”

思路：由TODO清单表分别构建3个负荷忙碌定义的子表，然后用日期表作为基表取关联，然后进行计数就好了。

query = '''
SELECT t.date, DATE(t.date,'+3 days'),SUM(t1.event_num),COUNT(t2.event),COUNT(t3.event)
FROM calendar_table AS t
LEFT JOIN (
  SELECT date,COUNT(event) event_num FROM evil_table WHERE desc!='结束' GROUP BY date
) AS t1 on t1.date = t.date
LEFT JOIN (
  SELECT date,event FROM evil_table WHERE importance>1 AND desc = '结束'
) AS t2 on t2.date BETWEEN DATE(t.date,'+1 days') AND DATE(t.date,'+3 days')
LEFT JOIN (
  SELECT date,event FROM evil_table WHERE importance>2 AND desc = '开始'
) AS t3 on t3.date BETWEEN DATE(t.date,'+1 days') AND DATE(t.date,'+3 days')
GROUP BY t.date
HAVING SUM(t1.event_num) >=2 or COUNT(t2.event)>0 or COUNT(t3.event)>0
order by t.date
'''
result = connection.execute(query).fetchall()
result

[('2023-01-03', '2023-01-06', 2, 1, 0),
 ('2023-01-04', '2023-01-07', 2, 1, 0),
 ('2023-01-05', '2023-01-08', 2, 1, 0),
 ('2023-01-06', '2023-01-09', 1, 0, 1),
 ('2023-01-07', '2023-01-10', 1, 0, 1),
 ('2023-01-08', '2023-01-11', 1, 0, 1),
 ('2023-01-09', '2023-01-12', 2, 0, 0),
 ('2023-01-10', '2023-01-13', 2, 0, 0),
 ('2023-01-11', '2023-01-14', 2, 0, 0),
 ('2023-01-12', '2023-01-15', 2, 0, 0),
 ('2023-01-13', '2023-01-16', 2, 0, 0),
 ('2023-01-14', '2023-01-17', 2, 0, 0),
 ('2023-01-15', '2023-01-18', 2, 0, 0),
 ('2023-01-16', '2023-01-19', 2, 0, 0),
 ('2023-01-17', '2023-01-20', 2, 0, 0),
 ('2023-01-18', '2023-01-21', 2, 1, 0),
 ('2023-01-19', '2023-01-22', 2, 1, 0),
 ('2023-01-20', '2023-01-23', 2, 1, 0),
 ('2023-02-08', '2023-02-11', None, 0, 1),
 ('2023-02-09', '2023-02-12', None, 0, 1),
 ('2023-02-10', '2023-02-13', 1, 0, 1),
 ('2023-02-11', '2023-02-14', 2, 0, 0),
 ('2023-02-12', '2023-02-15', 2, 0, 0),
 ('2023-02-13', '2023-02-16', 2, 0, 0),
 ('2023-02-14', '2023-02-17', 2, 0, 0),
 ('2023-02-15', '2023-02-18', 2, 0, 0),
 ('2023-02-16', '2023-02-19', 2, 0, 0),
 ('2023-02-17', '2023-02-20', 2, 0, 0),
 ('2023-02-18', '2023-02-21', 2, 0, 0),
 ('2023-02-19', '2023-02-22', 2, 0, 0),
 ('2023-02-20', '2023-02-23', 2, 0, 0),
 ('2023-02-21', '2023-02-24', 2, 0, 0),
 ('2023-02-22', '2023-02-25', 2, 0, 0),
 ('2023-02-23', '2023-02-26', 3, 0, 0),
 ('2023-02-24', '2023-02-27', 3, 1, 0),
 ('2023-02-25', '2023-02-28', 3, 1, 0),
 ('2023-02-26', '2023-03-01', 3, 1, 0),
 ('2023-02-27', '2023-03-02', 2, 0, 0),
 ('2023-02-28', '2023-03-03', 2, 0, 0),
 ('2023-03-01', '2023-03-04', 2, 0, 0),
 ('2023-03-02', '2023-03-05', 2, 0, 0),
 ('2023-03-03', '2023-03-06', 2, 0, 0),
 ('2023-03-04', '2023-03-07', 2, 0, 0),
 ('2023-03-05', '2023-03-08', 2, 0, 0),
 ('2023-03-06', '2023-03-09', 2, 0, 0),
 ('2023-03-07', '2023-03-10', 2, 0, 0),
 ('2023-03-08', '2023-03-11', 2, 0, 0),
 ('2023-03-09', '2023-03-12', 2, 0, 0),
 ('2023-03-10', '2023-03-13', 2, 1, 0),
 ('2023-03-11', '2023-03-14', 2, 1, 0),
 ('2023-03-12', '2023-03-15', 2, 1, 0),
 ('2023-03-17', '2023-03-20', 2, 0, 0),
 ('2023-03-18', '2023-03-21', 2, 0, 0),
 ('2023-03-19', '2023-03-22', 2, 0, 0),
 ('2023-03-20', '2023-03-23', 2, 0, 0),
 ('2023-03-21', '2023-03-24', 2, 0, 0),
 ('2023-03-22', '2023-03-25', 4, 0, 0),
 ('2023-03-23', '2023-03-26', 4, 0, 0),
 ('2023-03-24', '2023-03-27', 4, 1, 0),
 ('2023-03-25', '2023-03-28', 4, 1, 0),
 ('2023-03-26', '2023-03-29', 3, 1, 0),
 ('2023-03-27', '2023-03-30', 2, 0, 0),
 ('2023-03-28', '2023-03-31', 2, 0, 0),
 ('2023-03-29', '2023-04-01', 3, 0, 0),
 ('2023-03-30', '2023-04-02', 3, 0, 0),
 ('2023-03-31', '2023-04-03', 2, 0, 0),
 ('2023-04-01', '2023-04-04', 2, 0, 0),
 ('2023-04-02', '2023-04-05', 2, 0, 0),
 ('2023-04-03', '2023-04-06', 2, 0, 0),
 ('2023-04-04', '2023-04-07', 2, 0, 0),
 ('2023-04-05', '2023-04-08', 2, 0, 0),
 ('2023-04-06', '2023-04-09', 2, 0, 1),
 ('2023-04-07', '2023-04-10', 2, 0, 1),
 ('2023-04-08', '2023-04-11', 2, 0, 1),
 ('2023-04-09', '2023-04-12', 3, 0, 0),
 ('2023-04-10', '2023-04-13', 3, 1, 0),
 ('2023-04-11', '2023-04-14', 3, 1, 0),
 ('2023-04-12', '2023-04-15', 3, 1, 0),
 ('2023-04-13', '2023-04-16', 2, 0, 0),
 ('2023-04-14', '2023-04-17', 2, 0, 0),
 ('2023-04-15', '2023-04-18', 2, 1, 0),
 ('2023-04-16', '2023-04-19', 3, 1, 1),
 ('2023-04-17', '2023-04-20', 3, 1, 1),
 ('2023-04-18', '2023-04-21', 2, 0, 1),
 ('2023-04-19', '2023-04-22', 3, 0, 0),
 ('2023-04-20', '2023-04-23', 3, 0, 0),
 ('2023-04-21', '2023-04-24', 3, 0, 0),
 ('2023-04-22', '2023-04-25', 2, 0, 0),
 ('2023-04-23', '2023-04-26', 2, 0, 0),
 ('2023-04-24', '2023-04-27', 2, 1, 0),
 ('2023-04-25', '2023-04-28', 2, 1, 0),
 ('2023-04-26', '2023-04-29', 2, 1, 0),
 ('2023-04-29', '2023-05-02', 1, 0, 1),
 ('2023-04-30', '2023-05-03', 1, 0, 1),
 ('2023-05-01', '2023-05-04', 1, 0, 1),
 ('2023-05-02', '2023-05-05', 2, 0, 0),
 ('2023-05-03', '2023-05-06', 2, 0, 0),
 ('2023-05-04', '2023-05-07', 2, 0, 0),
 ('2023-05-05', '2023-05-08', 2, 0, 0),
 ('2023-05-06', '2023-05-09', 2, 0, 0),
 ('2023-05-07', '2023-05-10', 2, 1, 0),
 ('2023-05-08', '2023-05-11', 2, 1, 0),
 ('2023-05-09', '2023-05-12', 2, 1, 0),
 ('2023-05-12', '2023-05-15', 2, 0, 0),
 ('2023-05-13', '2023-05-16', 2, 0, 0),
 ('2023-05-14', '2023-05-17', 2, 0, 0),
 ('2023-05-15', '2023-05-18', 2, 0, 0),
 ('2023-05-16', '2023-05-19', 2, 0, 0),
 ('2023-05-17', '2023-05-20', 2, 0, 0),
 ('2023-05-18', '2023-05-21', 2, 0, 0),
 ('2023-05-19', '2023-05-22', 2, 0, 0),
 ('2023-05-20', '2023-05-23', 2, 0, 0),
 ('2023-05-21', '2023-05-24', 3, 0, 0),
 ('2023-05-22', '2023-05-25', 3, 0, 0),
 ('2023-05-23', '2023-05-26', 3, 0, 0),
 ('2023-05-24', '2023-05-27', 3, 0, 0),
 ('2023-05-25', '2023-05-28', 3, 0, 0),
 ('2023-05-26', '2023-05-29', 3, 0, 0),
 ('2023-05-27', '2023-05-30', 2, 0, 0),
 ('2023-05-28', '2023-05-31', 2, 0, 0),
 ('2023-05-29', '2023-06-01', 2, 0, 0),
 ('2023-05-30', '2023-06-02', 2, 0, 0),
 ('2023-05-31', '2023-06-03', 2, 0, 0),
 ('2023-06-01', '2023-06-04', 2, 0, 0),
 ('2023-06-02', '2023-06-05', 2, 0, 0),
 ('2023-06-03', '2023-06-06', 2, 0, 0),
 ('2023-06-04', '2023-06-07', 2, 0, 0),
 ('2023-06-08', '2023-06-11', 1, 0, 1),
 ('2023-06-09', '2023-06-12', 1, 0, 1),
 ('2023-06-10', '2023-06-13', 1, 0, 1),
 ('2023-06-11', '2023-06-14', 2, 0, 0),
 ('2023-06-12', '2023-06-15', 2, 0, 0),
 ('2023-06-13', '2023-06-16', 2, 0, 0),
 ('2023-06-14', '2023-06-17', 2, 0, 0),
 ('2023-06-21', '2023-06-24', 2, 1, 0),
 ('2023-06-22', '2023-06-25', 2, 1, 0),
 ('2023-06-23', '2023-06-26', 2, 1, 0),
 ('2023-07-02', '2023-07-05', 1, 0, 1),
 ('2023-07-03', '2023-07-06', 1, 0, 1),
 ('2023-07-04', '2023-07-07', 1, 0, 1),
 ('2023-07-05', '2023-07-08', 2, 0, 0),
 ('2023-07-06', '2023-07-09', 2, 0, 0),
 ('2023-07-07', '2023-07-10', 2, 0, 0),
 ('2023-07-08', '2023-07-11', 2, 0, 0),
 ('2023-07-09', '2023-07-12', 2, 0, 0),
 ('2023-07-10', '2023-07-13', 2, 0, 0),
 ('2023-07-11', '2023-07-14', 2, 0, 0),
 ('2023-07-12', '2023-07-15', 2, 0, 0),
 ('2023-07-13', '2023-07-16', 3, 0, 0),
 ('2023-07-14', '2023-07-17', 3, 0, 0),
 ('2023-07-15', '2023-07-18', 3, 0, 0),
 ('2023-07-16', '2023-07-19', 3, 0, 0),
 ('2023-07-17', '2023-07-20', 3, 0, 0),
 ('2023-07-18', '2023-07-21', 3, 0, 0),
 ('2023-07-19', '2023-07-22', 4, 0, 0),
 ('2023-07-20', '2023-07-23', 3, 0, 0),
 ('2023-07-21', '2023-07-24', 3, 0, 0),
 ('2023-07-22', '2023-07-25', 3, 0, 0),
 ('2023-07-23', '2023-07-26', 3, 1, 0),
 ('2023-07-24', '2023-07-27', 3, 1, 0),
 ('2023-07-25', '2023-07-28', 3, 1, 0),
 ('2023-07-26', '2023-07-29', 3, 0, 0),
 ('2023-07-27', '2023-07-30', 3, 0, 0),
 ('2023-07-28', '2023-07-31', 3, 0, 0),
 ('2023-07-29', '2023-08-01', 3, 1, 0),
 ('2023-07-30', '2023-08-02', 3, 1, 0),
 ('2023-07-31', '2023-08-03', 3, 1, 0),
 ('2023-08-01', '2023-08-04', 2, 0, 0),
 ('2023-08-05', '2023-08-08', 1, 1, 0),
 ('2023-08-06', '2023-08-09', 1, 1, 0),
 ('2023-08-07', '2023-08-10', 1, 1, 0),
 ('2023-08-11', '2023-08-14', None, 0, 1),
 ('2023-08-12', '2023-08-15', None, 0, 1),
 ('2023-08-13', '2023-08-16', None, 0, 1),
 ('2023-08-18', '2023-08-21', 2, 0, 0),
 ('2023-08-19', '2023-08-22', 2, 0, 0),
 ('2023-08-20', '2023-08-23', 2, 0, 0),
 ('2023-08-21', '2023-08-24', 2, 0, 0),
 ('2023-08-22', '2023-08-25', 3, 0, 0),
 ('2023-08-23', '2023-08-26', 3, 0, 0),
 ('2023-08-24', '2023-08-27', 3, 0, 0),
 ('2023-08-25', '2023-08-28', 3, 1, 1),
 ('2023-08-26', '2023-08-29', 3, 1, 1),
 ('2023-08-27', '2023-08-30', 2, 1, 1),
 ('2023-08-28', '2023-08-31', 2, 0, 0),
 ('2023-08-29', '2023-09-01', 2, 0, 0),
 ('2023-08-30', '2023-09-02', 2, 0, 0),
 ('2023-08-31', '2023-09-03', 3, 0, 0),
 ('2023-09-01', '2023-09-04', 4, 0, 0),
 ('2023-09-02', '2023-09-05', 4, 0, 0),
 ('2023-09-03', '2023-09-06', 4, 0, 0),
 ('2023-09-04', '2023-09-07', 4, 0, 1),
 ('2023-09-05', '2023-09-08', 4, 1, 1),
 ('2023-09-06', '2023-09-09', 5, 1, 1),
 ('2023-09-07', '2023-09-10', 6, 1, 0),
 ('2023-09-08', '2023-09-11', 5, 0, 0),
 ('2023-09-09', '2023-09-12', 5, 0, 0),
 ('2023-09-10', '2023-09-13', 6, 0, 0),
 ('2023-09-11', '2023-09-14', 6, 0, 0),
 ('2023-09-12', '2023-09-15', 6, 0, 0),
 ('2023-09-13', '2023-09-16', 6, 0, 0),
 ('2023-09-14', '2023-09-17', 5, 0, 0),
 ('2023-09-15', '2023-09-18', 4, 0, 0),
 ('2023-09-16', '2023-09-19', 4, 0, 0),
 ('2023-09-17', '2023-09-20', 4, 0, 0),
 ('2023-09-18', '2023-09-21', 4, 0, 0),
 ('2023-09-19', '2023-09-22', 4, 1, 0),
 ('2023-09-20', '2023-09-23', 4, 1, 0),
 ('2023-09-21', '2023-09-24', 4, 1, 0),
 ('2023-09-22', '2023-09-25', 3, 0, 0),
 ('2023-09-23', '2023-09-26', 3, 0, 0),
 ('2023-09-24', '2023-09-27', 3, 0, 0),
 ('2023-09-29', '2023-10-02', 1, 1, 0),
 ('2023-09-30', '2023-10-03', 1, 1, 0),
 ('2023-10-01', '2023-10-04', 1, 1, 0),
 ('2023-10-08', '2023-10-11', 2, 0, 0),
 ('2023-10-09', '2023-10-12', 2, 0, 1),
 ('2023-10-10', '2023-10-13', 2, 0, 1),
 ('2023-10-11', '2023-10-14', 2, 0, 1),
 ('2023-10-12', '2023-10-15', 3, 0, 0),
 ('2023-10-13', '2023-10-16', 2, 0, 0),
 ('2023-10-14', '2023-10-17', 2, 0, 0),
 ('2023-10-15', '2023-10-18', 2, 0, 0),
 ('2023-10-16', '2023-10-19', 2, 0, 0),
 ('2023-10-17', '2023-10-20', 2, 0, 0),
 ('2023-10-18', '2023-10-21', 2, 0, 0),
 ('2023-10-19', '2023-10-22', 2, 1, 0),
 ('2023-10-20', '2023-10-23', 2, 1, 0),
 ('2023-10-21', '2023-10-24', 2, 1, 0),
 ('2023-11-15', '2023-11-18', 1, 0, 1),
 ('2023-11-16', '2023-11-19', 1, 0, 1),
 ('2023-11-17', '2023-11-20', 1, 0, 1),
 ('2023-11-18', '2023-11-21', 2, 0, 0),
 ('2023-11-19', '2023-11-22', 2, 0, 0),
 ('2023-11-20', '2023-11-23', 2, 0, 0),
 ('2023-11-21', '2023-11-24', 2, 0, 0),
 ('2023-11-22', '2023-11-25', 2, 0, 0),
 ('2023-11-23', '2023-11-26', 2, 0, 0),
 ('2023-11-24', '2023-11-27', 2, 0, 0),
 ('2023-11-25', '2023-11-28', 2, 0, 0),
 ('2023-11-26', '2023-11-29', 2, 0, 0),
 ('2023-11-27', '2023-11-30', 2, 0, 0),
 ('2023-11-28', '2023-12-01', 2, 0, 0),
 ('2023-11-29', '2023-12-02', 2, 0, 0),
 ('2023-11-30', '2023-12-03', 3, 0, 0),
 ('2023-12-01', '2023-12-04', 3, 0, 0),
 ('2023-12-02', '2023-12-05', 3, 0, 0),
 ('2023-12-03', '2023-12-06', 3, 1, 0),
 ('2023-12-04', '2023-12-07', 4, 1, 0),
 ('2023-12-05', '2023-12-08', 4, 1, 0),
 ('2023-12-06', '2023-12-09', 3, 0, 0),
 ('2023-12-07', '2023-12-10', 3, 0, 0),
 ('2023-12-08', '2023-12-11', 3, 0, 0),
 ('2023-12-09', '2023-12-12', 3, 0, 0),
 ('2023-12-10', '2023-12-13', 3, 0, 0),
 ('2023-12-11', '2023-12-14', 3, 0, 0),
 ('2023-12-12', '2023-12-15', 3, 0, 0),
 ('2023-12-13', '2023-12-16', 3, 0, 0),
 ('2023-12-14', '2023-12-17', 3, 0, 0),
 ('2023-12-15', '2023-12-18', 3, 0, 0),
 ('2023-12-16', '2023-12-19', 3, 0, 0),
 ('2023-12-17', '2023-12-20', 3, 0, 0),
 ('2023-12-18', '2023-12-21', 3, 1, 0),
 ('2023-12-19', '2023-12-22', 3, 1, 0),
 ('2023-12-20', '2023-12-23', 3, 1, 0),
 ('2023-12-21', '2023-12-24', 2, 0, 0),
 ('2023-12-22', '2023-12-25', 2, 0, 0),
 ('2023-12-23', '2023-12-26', 2, 1, 0),
 ('2023-12-24', '2023-12-27', 4, 2, 0),
 ('2023-12-25', '2023-12-28', 4, 2, 0),
 ('2023-12-26', '2023-12-29', 1, 1, 0),
 ('2023-12-31', '2024-01-03', 2, 1, 0)]

len(result)

272

# ...your code...
a8 = '272'  # 在 '' 中填入你的结果，如 a8 = '5' 代表有 5 天忙碌
Q9: 在 2023 年当中，有多少个周末我需要执行 >= 2个的事项？

例如，某一周的 周六执行 3 个事项，周日执行 1 个事项，则记为 1 个周末（周六算，周日不算）

思路：就是求有多少个周六周日event的数量≥2而已。日期表(简称t1) 关联 TODO清单表(简称t2)，关联时t2表需要舍弃状态为“结束”的行【①号坑】。关联后条件筛选t1的weekday为周六或者周日，基于t1的week和weekday对t2的event进行计数，并筛选计数项≥2的行。

query = '''
SELECT t1.week_number,t1.week_day,count(DISTINCT t2.event)
FROM calendar_table t1
LEFT JOIN evil_table t2 on t2.date = t1.date
WHERE t1.week_day in (6,7) and t2.desc != '结束'
GROUP BY t1.week_number,t1.week_day
HAVING count(distinct t2.event) >=2
'''
result = connection.execute(query).fetchall()
result

[(3, 6, 2),
 (3, 7, 2),
 (7, 6, 2),
 (7, 7, 2),
 (8, 6, 2),
 (8, 7, 2),
 (9, 6, 3),
 (9, 7, 3),
 (10, 6, 2),
 (10, 7, 2),
 (11, 6, 2),
 (11, 7, 2),
 (12, 6, 2),
 (12, 7, 2),
 (13, 6, 4),
 (13, 7, 3),
 (14, 6, 2),
 (14, 7, 2),
 (15, 6, 2),
 (15, 7, 3),
 (16, 6, 2),
 (16, 7, 3),
 (17, 6, 2),
 (17, 7, 2),
 (19, 6, 2),
 (19, 7, 2),
 (20, 6, 2),
 (20, 7, 2),
 (21, 6, 2),
 (21, 7, 3),
 (22, 6, 2),
 (22, 7, 2),
 (23, 6, 2),
 (23, 7, 2),
 (24, 7, 2),
 (28, 6, 2),
 (28, 7, 2),
 (29, 6, 3),
 (29, 7, 3),
 (30, 6, 3),
 (30, 7, 3),
 (31, 6, 3),
 (31, 7, 3),
 (34, 6, 2),
 (34, 7, 2),
 (35, 6, 3),
 (35, 7, 2),
 (36, 6, 4),
 (36, 7, 4),
 (37, 6, 5),
 (37, 7, 6),
 (38, 6, 4),
 (38, 7, 4),
 (39, 6, 3),
 (39, 7, 3),
 (41, 7, 2),
 (42, 6, 2),
 (42, 7, 2),
 (43, 6, 2),
 (47, 6, 2),
 (47, 7, 2),
 (48, 6, 2),
 (48, 7, 2),
 (49, 6, 3),
 (49, 7, 3),
 (50, 6, 3),
 (50, 7, 3),
 (51, 6, 3),
 (51, 7, 3),
 (52, 6, 2),
 (52, 7, 2),
 (53, 7, 2)]

len(result)

72

# ...your code...
a9 = '72'  # 在 '' 中填入你的结果，如 a9 = '5' 代表有 5 个周末要执行2个不同的事项（周六，周日分开计算，例如 2个周六 和 3个周日 = 5个周末）
Q10: 在 2023 年当中，我的繁忙系数是多少？

繁忙系数 = ∑ i = 1 繁忙天数 每日繁忙系数 繁忙系数 = \sum_{i=1}^{繁忙天数}每日繁忙系数 繁忙系数=i=1∑繁忙天数​每日繁忙系数

结果只需要保留整数位，不需要四舍五入，例如：若答案为 221.229，则回答 221 即可

忙碌的天数定义同 Q8

每日繁忙系数 = （当日事项重要性的平均值* 0.5+未来 3 天内要开展的事项的重要性的平均值 * 0.3 + 未来 3 天内要结束的事项的重要性的平均值 * 0.2）

思路：避开坑后，就有思路了。同Q8，分别列出满足3个条件的子表，然后用日历表为基准去关联，最后根据算法求出每天的繁忙系数然后累加即可。

query = '''
SELECT sum(index1*0.5+index2*0.3+index3*0.2) indexAll FROM(
SELECT t.date, DATE(t.date,'+3 days'),IFNULL(t1.imp,0) index1,IFNULL(AVG(t3.importance),0) index2,IFNULL(AVG(t2.importance),0) index3
FROM calendar_table AS t 
LEFT JOIN (
  SELECT date,AVG(importance) imp FROM evil_table WHERE desc != '结束' GROUP BY date HAVING COUNT(event)>=2
) AS t1 on t1.date = t.date
LEFT JOIN (
  SELECT date,importance FROM evil_table WHERE importance>1 AND desc = '结束'
) AS t2 on t2.date BETWEEN DATE(t.date,'+1 days') AND DATE(t.date,'+3 days')
LEFT JOIN (
  SELECT date,importance FROM evil_table WHERE importance>2 AND desc = '开始'
) AS t3 on t3.date BETWEEN DATE(t.date,'+1 days') AND DATE(t.date,'+3 days')
GROUP BY t.date
order by t.date
) as a
'''
result = connection.execute(query).fetchall()
result

[(252.50833333333347,)]

# ...your code...
a10 = '252'  # 在 '' 中填入你的结果，如 a10 = '12' 代表忙碌系数为 12
大地之灯
关注
专栏目录
SQL进阶技巧：如何提取JSON中 key值
石榴姐yyds
08-18 2556
我想获取该json中的每一个key值，如何做？如下json_str。
SQL创建日历 包含年月日/周/星期几
qq_18193817的博客
02-18 1187
CREATE TABLE date ( id INT IDENTITY (1,1), date NVARCHAR(50), year INT, month INT, day INT, week INT , weekday NVARCHAR(10) ) DECLARE @NowDATE DATETIME=‘2022.01.01 00:00:00’ DECLARE @i INT = 1 WHILE @i<=3650 BEGIN INSERT INTO dbo.date ( date, year, m
利用sql server直接创建日历
snptc的博客
02-26 525
看到网上有高手直接用sql查询创建日历，也想自己动手实践一遍。笔者这里的实现和网上的都没有什么区别，思路也没有什么新意。觉得好玩，就把它记下来吧。 一、准备知识 1、sql的with关键字 关于with和公用表表达式（CTE），可以参考SQL Server 2005新特性之使用with关键字解决递归父子关系和 Sql Server2005 Transact-SQL 新兵器学习总结之-公用表表达式(...
通过sql返回指定月份的日历，包含日期、周几，以周日开头，不足的补全上月和下月的日历
gongjin28_csdn的博客
04-15 376
在上面的查询中，将TO_DATE(‘2023-07-01’, ‘YYYY-MM-DD’)替换为所需月份的第一天。查询将生成从指定月份的第一天开始到月底的日历，并以周日开头。如果指定月份的第一天不是周日，则会补全上月的最后一天。如果指定月份的最后一天不是周日，则会补全下月的第一天。结果按日历日期排序。在Oracle SQL中，可以使用CONNECT BY和ROWNUM来生成指定月份的日历，并通过TO_CHAR函数将日期转换为周日开头的格式。
复杂业务查询
qq_41354978的博客
06-28 214
一、计算出订单在哪个时间区间(以区间进行分组) CEIL(TIMESTAMPDIFF(SECOND,'2019-05-10 00:00:00',creatTime)/20) num 1、查询下单频率过快 （一段时间内范围内，每间隔时间t秒，订单数量超出n单则为异常单） （2）以时间区间分组 SELECT d.`no` FROM o_delivery d INNER JOIN ( ...
跳过多少个工作日sql实现
s07aser123的专栏
03-04 450
跳过多少个工作日，sql实现方案
SQL进阶技巧：如何计算连续增长问题？
石榴姐yyds
07-25 3103
本文分了连续性增长这一指标的计算方法，在实际业务中经常会遇到，作为面试题也可以考察面试者的素养，因而掌握该类题的解法具有重要意义。本题要求解的是连续增长，那么条件就是连续的时间和销售金额的连续增长，求解如下。我们需要计算过去至少连续3天销售金额连续增长的商户。假设我们有一张订单表shop_order。
SQL进阶技巧：如何分区间或分时段统计数据？| 等距分桶问题应用
石榴姐yyds
06-15 2999
0 需求 有一批交易数据，并希望每5秒中汇总一下这些数据。trx_log数据如下： trx_id trx_date trx_cnt 1 28-JUL-2005 19:03:07 44 2 28-JUL-2005 19:03:08 18 3 28-JUL-2005 19:03:09 23 4 28-JUL-2005 19:03:10 29 5 28-JUL-2005 19:03:11 27 6 28-JU
SQL进阶技巧：如何求波峰和波谷？
石榴姐yyds
07-20 2707
目录 0 需求 1 数据准备 2 数据分析 3 小 结 0 需求：求股票的波峰Crest 和 波谷trough 波峰：当天的股票价格大于前一天和后一天 波谷：当天的股票价格小于前一天和后一天 数据准备： stock表有3个字段： id（股票id）、price（股票价格）、dt（日期） --原始数据 stock表------- id price dt 1 20 2021-07-01 1 21 2021-07-02 1 19 20
SQL如何制作一张日历维度表
SQL数据库开发
08-02 2419
点击上方SQL数据库开发，关注获取SQL视频教程SQL专栏SQL数据库基础知识汇总SQL数据库高级知识汇总问题描述输入想要生成日历的年份，通过调用存储过程，即可生成该年的...
获取这个当前月份的第一天和最后一天，JS获取日期(年/月/日/时/分/秒)&格式转化
qq_36597079的博客
12-14 5589
所以翻查资料之后借鉴网上的资源整理了下，具体方法如下 function getMonthFirstLastDay(year,month){ var firstDay=new Date(year,month-1,1);//这个月的第一天 var currentMonth=firstDay.getMonth(); //取得月份数 var nextMonthFirstDay=...
SQL生成日历
coolmsn8786的博客
11-11 224
http://jackywood.itpub.net/post/1369/127565 一条SQL语句生成年历。 select case whe...
sql server 创建日历表
xiaozaq的博客
03-10 3561
sql server 创建日历表： CREATE TABLE [dbo].[Calendar] ( [time_id] [int] IDENTITY (1, 1) NOT NULL primary key, [the_date] [datetime] NULL , [the_day] [nvarchar] (15) NULL , [the_month] [nvar
mysql sql语句生成日历表
热门推荐
hins
10-11 1万+
mysql sql语句生成日历表  （主要用于按月，按天group by分组统计时，有些日期没有记录，需要补0） 从自定义的开始时间，生成1万条记录 CREATE TABLE if not exists num (i int);-- 创建一个表用来储存0-9的数字 INSERT INTO num (i) VALUES (0), (1), (2), (3), (4), (5), (6), (
SQL创建日历表
jinma425的专栏
06-25 1938
SELECT DATENAME(yy,GETDATE()) --当前年 SELECT DATENAME(mm,GETDATE()) --当前月 SELECT DATENAME(dd,GETDATE()) --当前号 SELECT DATENAME(week,GETDATE()) --当前年的多少周 SELECT DATENAME (dw, GETDATE()) --星期几 SELECT '第' ...
【Hive sql面试题】找出连续活跃3天及以上的用户
最新发布
lzhlizihang的博客
10-31 37
Hive sql面试题：找出连续活跃3天及以上的用户，包括解题思路以及答案 sql 代码
SQL预编译的原理，以及通过预编译能防止哪些SQL注入，不能防止哪些SQL注入
weixin_45945976的博客
10-31 224
在这些情况下，需要结合其他安全措施，如输入验证、过滤和使用白名单等，来进一步加强防护。预编译能有效预防那些通过改变SQL语句结构来实现的注入攻击，因为它将用户输入作为参数处理，而不是SQL代码的一部分。预编译虽然有效，但它不能防御所有类型的SQL注入，特别是在处理动态表名、列名或排序方式等无法使用占位符替代的部分。预编译（Prepared Statement）是一种数据库技术，它允许在执行SQL语句之前，先对SQL语句的结构和查询逻辑进行预先处理。在Go语言中，这通常是通过。
评论 1
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值