【Python百日进阶-Web开发-Peewee】Day258 - Peewee 窗口函数

岳涛@心馨电脑

于 2023-07-12 08:27:31 发布

阅读量261

点赞数

分类专栏：数据库文章标签： python 前端开发语言

本文链接：https://blog.csdn.net/yuetaope/article/details/124021749

版权

数据库专栏收录该内容

43 篇文章 1 订阅

订阅专栏

文章目录

8.15 检索标量值

您可以通过调用来检索标量值Query.scalar()。例如：

>>> PageView.select(fn.Count(fn.Distinct(PageView.url))).scalar()
100

您可以通过传递来检索多个标量值as_tuple=True：

>>> Employee.select(
...     fn.Min(Employee.salary), fn.Max(Employee.salary)
... ).scalar(as_tuple=True)
(30000, 50000)

8.16 窗口函数

Window函数是指对作为查询的一部分进行处理的数据的滑动窗口进行操作的聚合函数SELECT。窗口函数可以执行以下操作：

对结果集的子集执行聚合。
计算运行总数。
排名结果。
将行值与前面（或后面！）行中的值进行比较。
peewee 支持 SQL 窗口函数，可以通过调用Function.over()和传入分区或排序参数来创建。

对于以下示例，我们将使用以下模型和示例数据：

class Sample(Model):
    counter = IntegerField()
    value = FloatField()

data = [(1, 10),
        (1, 20),
        (2, 1),
        (2, 3),
        (3, 100)]
Sample.insert_many(data, fields=[Sample.counter, Sample.value]).execute()

我们的示例表现在包含：

ID	counter	value
1	1	10.0
2	1	20.0
3	2	1.0
4	2	3.0
5	3	100.0

8.16.1 Ordered Windows

让我们计算该value字段的运行总和。为了使其成为“运行”总和，我们需要对其进行排序，因此我们将针对 Sample 的id字段进行排序：

query = Sample.select(
    Sample.counter,
    Sample.value,
    fn.SUM(Sample.value).over(order_by=[Sample.id]).alias('total'))

for sample in query:
    print(sample.counter, sample.value, sample.total)

# 1    10.    10.
# 1    20.    30.
# 2     1.    31.
# 2     3.    34.
# 3   100    134.

再举一个例子，我们将计算当前值和前一个值之间的差，当按以下顺序排序时id：

difference = Sample.value - fn.LAG(Sample.value, 1).over(order_by=[Sample.id])
query = Sample.select(
    Sample.counter,
    Sample.value,
    difference.alias('diff'))

for sample in query:
    print(sample.counter, sample.value, sample.diff)

# 1    10.   NULL
# 1    20.    10.  -- (20 - 10)
# 2     1.   -19.  -- (1 - 20)
# 2     3.     2.  -- (3 - 1)
# 3   100     97.  -- (100 - 3)

8.16.2 分区窗口

让我们计算value每个不同“计数器”值的平均值。请注意，该字段有三个可能的值counter（1、2 和 3）。我们可以通过计算根据字段划分的窗口上AVG()的列的来做到这一点：valuecounter

query = Sample.select(
    Sample.counter,
    Sample.value,
    fn.AVG(Sample.value).over(partition_by=[Sample.counter]).alias('cavg'))

for sample in query:
    print(sample.counter, sample.value, sample.cavg)

# 1    10.    15.
# 1    20.    15.
# 2     1.     2.
# 2     3.     2.
# 3   100    100.

我们可以通过指定order_by和 partition_by参数在分区内使用排序。例如，让我们按每个不同counter组中的值对样本进行排名。

query = Sample.select(
    Sample.counter,
    Sample.value,
    fn.RANK().over(
        order_by=[Sample.value],
        partition_by=[Sample.counter]).alias('rank'))

for sample in query:
    print(sample.counter, sample.value, sample.rank)

# 1    10.    1
# 1    20.    2
# 2     1.    1
# 2     3.    2
# 3   100     1

8.16.3 有界窗口

默认情况下，窗口函数使用窗口的无界前开始和当前行作为结束来评估。我们可以通过start在 end对Function.over(). 此外，Peewee 在对象上附带了辅助方法，Window用于生成适当的边界引用：

Window.CURRENT_ROW- 引用当前行的属性。
Window.preceding()- 指定前面的行数，或省略数字以指示所有前面的行。
Window.following()- 指定后面的行数，或省略数字以指示所有后续行。
为了检查边界是如何工作的，我们将计算 value列的运行总计，相对于排序id，但我们只会查看当前行的运行总计，它是前两行：

query = Sample.select(
    Sample.counter,
    Sample.value,
    fn.SUM(Sample.value).over(
        order_by=[Sample.id],
        start=Window.preceding(2),
        end=Window.CURRENT_ROW).alias('rsum'))

for sample in query:
    print(sample.counter, sample.value, sample.rsum)

# 1    10.    10.
# 1    20.    30.  -- (20 + 10)
# 2     1.    31.  -- (1 + 20 + 10)
# 2     3.    24.  -- (3 + 1 + 20)
# 3   100    104.  -- (100 + 3 + 1)

笔记

从技术上讲，我们不需要指定，end=Window.CURRENT因为这是默认设置。它在示例中进行了演示。

让我们看另一个例子。在此示例中，我们将计算运行总计的“相反”，其中所有值的总和减去样本的值，按 . 排序id。为此，我们将计算从当前行到最后一行的总和。

query = Sample.select(
    Sample.counter,
    Sample.value,
    fn.SUM(Sample.value).over(
        order_by=[Sample.id],
        start=Window.CURRENT_ROW,
        end=Window.following()).alias('rsum'))

# 1    10.   134.  -- (10 + 20 + 1 + 3 + 100)
# 1    20.   124.  -- (20 + 1 + 3 + 100)
# 2     1.   104.  -- (1 + 3 + 100)
# 2     3.   103.  -- (3 + 100)
# 3   100    100.  -- (100)

8.16.4 过滤聚合

聚合函数也可能支持过滤函数（Postgres 和 Sqlite 3.25+），它们被翻译成一个子句。使用该方法将过滤器表达式添加到聚合函数中。FILTER (WHERE…)Function.filter()

例如，我们将计算value字段相对于的运行总和id，但我们将过滤掉任何具有counter=2.

query = Sample.select(
    Sample.counter,
    Sample.value,
    fn.SUM(Sample.value).filter(Sample.counter != 2).over(
        order_by=[Sample.id]).alias('csum'))

for sample in query:
    print(sample.counter, sample.value, sample.csum)

# 1    10.    10.
# 1    20.    30.
# 2     1.    30.
# 2     3.    30.
# 3   100    130.

笔记

对的调用filter()必须在对的调用之前 over()。

8.16.5 重用窗口定义

如果您打算对多个聚合使用相同的窗口定义，您可以创建一个Window对象。该Window对象采用与相同的参数Function.over()，并且可以 over()代替单个参数传递给方法。

在这里，我们将声明一个单独的窗口，根据 sample 进行排序id，并使用该窗口定义调用多个窗口函数：

win = Window(order_by=[Sample.id])
query = Sample.select(
    Sample.counter,
    Sample.value,
    fn.LEAD(Sample.value).over(win),
    fn.LAG(Sample.value).over(win),
    fn.SUM(Sample.value).over(win)
).window(win)  # Include our window definition in query.

for row in query.tuples():
    print(row)

# counter  value  lead()  lag()  sum()
# 1          10.     20.   NULL    10.
# 1          20.      1.    10.    30.
# 2           1.      3.    20.    31.
# 2           3.    100.     1.    34.
# 3         100.    NULL     3.   134.

8.16.6 多个窗口定义

在前面的示例中，我们看到了如何声明Window定义并将其重用于多个不同的聚合。您可以在查询中包含任意数量的窗口定义，但必须确保每个窗口都有唯一的别名：

w1 = Window(order_by=[Sample.id]).alias('w1')
w2 = Window(partition_by=[Sample.counter]).alias('w2')
query = Sample.select(
    Sample.counter,
    Sample.value,
    fn.SUM(Sample.value).over(w1).alias('rsum'),  # Running total.
    fn.AVG(Sample.value).over(w2).alias('cavg')   # Avg per category.
).window(w1, w2)  # Include our window definitions.

for sample in query:
    print(sample.counter, sample.value, sample.rsum, sample.cavg)

# counter  value   rsum     cavg
# 1          10.     10.     15.
# 1          20.     30.     15.
# 2           1.     31.      2.
# 2           3.     34.      2.
# 3         100     134.    100.

同样，如果您有多个共享相似定义的窗口定义，则可以扩展先前定义的窗口定义。例如，这里我们将按计数器值对数据集进行分区，因此我们将对计数器进行聚合。然后我们将定义第二个窗口来扩展这个分区，并添加一个排序子句：

w1 = Window(partition_by=[Sample.counter]).alias('w1')

# By extending w1, this window definition will also be partitioned
# by "counter".
w2 = Window(extends=w1, order_by=[Sample.value.desc()]).alias('w2')

query = (Sample
         .select(Sample.counter, Sample.value,
                 fn.SUM(Sample.value).over(w1).alias('group_sum'),
                 fn.RANK().over(w2).alias('revrank'))
         .window(w1, w2)
         .order_by(Sample.id))

for sample in query:
    print(sample.counter, sample.value, sample.group_sum, sample.revrank)

# counter  value   group_sum   revrank
# 1        10.     30.         2
# 1        20.     30.         1
# 2        1.      4.          2
# 2        3.      4.          1
# 3        100.    100.        1

8.16.7 帧类型：RANGE vs ROWS vs GROUPS

根据帧类型，数据库将以不同方式处理有序组。让我们创建两个额外的Sample行来可视化差异：

>>> Sample.create(counter=1, value=20.)
<Sample 6>
>>> Sample.create(counter=2, value=1.)
<Sample 7>

我们的表现在包含：

ID	counter	value
1	1	10.0
2	1	20.0
3	2	1.0
4	2	3.0
5	3	100.0
6	1	20.0
7	2	1.0
让我们通过计算样本的“运行总和”来检查差异，按照counter和value字段排序。要指定帧类型，我们可以使用：

Window.RANGE
Window.ROWS
Window.GROUPS
的行为RANGE，当存在逻辑重复时，可能会导致意想不到的结果：

query = Sample.select(
    Sample.counter,
    Sample.value,
    fn.SUM(Sample.value).over(
        order_by=[Sample.counter, Sample.value],
        frame_type=Window.RANGE).alias('rsum'))

for sample in query.order_by(Sample.counter, Sample.value):
    print(sample.counter, sample.value, sample.rsum)

# counter  value   rsum
# 1          10.     10.
# 1          20.     50.
# 1          20.     50.
# 2           1.     52.
# 2           1.     52.
# 2           3.     55.
# 3         100     155.

随着新行的包含，我们现在有一些具有重复值category和value值的行。帧类型导致这些RANGE重复项一起评估，而不是单独评估。

ROWS使用as frame-type可以获得更预期的结果：

query = Sample.select(
    Sample.counter,
    Sample.value,
    fn.SUM(Sample.value).over(
        order_by=[Sample.counter, Sample.value],
        frame_type=Window.ROWS).alias('rsum'))

for sample in query.order_by(Sample.counter, Sample.value):
    print(sample.counter, sample.value, sample.rsum)

# counter  value   rsum
# 1          10.     10.
# 1          20.     30.
# 1          20.     50.
# 2           1.     51.
# 2           1.     52.
# 2           3.     55.
# 3         100     155.

Peewee 使用这些规则来确定要使用的帧类型：

如果用户指定 a frame_type，则将使用该帧类型。
如果start和/或end边界被指定，Peewee 将默认使用ROWS.
如果用户没有指定帧类型或开始/结束边界，Peewee 将使用数据库默认值，即RANGE.
Window.GROUPS框架类型根据排序项以行组的形式查看窗口范围规范。使用GROUPS，我们可以定义框架，使其覆盖不同的行分组。让我们看一个例子：

query = (Sample
         .select(Sample.counter, Sample.value,
                 fn.SUM(Sample.value).over(
                    order_by=[Sample.counter, Sample.value],
                    frame_type=Window.GROUPS,
                    start=Window.preceding(1)).alias('gsum'))
         .order_by(Sample.counter, Sample.value))

for sample in query:
    print(sample.counter, sample.value, sample.gsum)

#  counter   value    gsum
#  1         10       10
#  1         20       50
#  1         20       50   (10) + (20+0)
#  2         1        42
#  2         1        42   (20+20) + (1+1)
#  2         3        5    (1+1) + 3
#  3         100      103  (3) + 100

正如您所希望的那样，窗口按其排序项分组，即. 我们正在查看一个在前一个组和当前组之间延伸的窗口。(counter, value)

笔记

有关窗口函数 API 的信息，请参阅：

Function.over()
Function.filter() - Window
有关窗口函数的一般信息，请阅读
postgres窗口函数教程

此外，postgres 文档和sqlite 文档包含很多有用的信息。

岳涛@心馨电脑

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
打赏
0
评论
【Python百日进阶-Web开发-Peewee】Day258 - Peewee 窗口函数

文章目录8.15 检索标量值8.16 窗口函数8.16.1 Ordered Windows8.16.2 分区窗口8.16.3 有界窗口8.16.4 过滤聚合8.16.5 重用窗口定义8.16.6 多个窗口定义8.16.7 帧类型：RANGE vs ROWS vs GROUPS8.15 检索标量值您可以通过调用来检索标量值Query.scalar()。例如：>>> PageView.select(fn.Count(fn.Distinct(PageView.url))).scalar()
复制链接

扫一扫