mysql - 具有窗口功能的 column_property(或其他额外的列)?
问题描述
我有以下情况,将 SQLAlchemy 与 MySQL 数据库一起使用。假设一个 Person 表如下所示:
+------+-----------+----------+------------+
| ID | firstname | lastname | startdate |
+------+-----------+----------+------------+
| 43 | Bob | Smith | 2016-12-04 |
| 873 | Mary | Jones | 2018-05-01 |
| 120 | Bob | Smith | 2020-04-02 |
| 339 | Bob | Jones | 2019-03-01 |
| 1022 | Bob | Smith | 2015-11-21 |
+------+-----------+----------+------------+
我需要生成一个具有以下属性的额外列(或任何等效解决方案):对于具有相同名字和姓氏的任何人,返回按开始日期排序的序列号。如果一个人不共享名字和姓氏,则返回 NULL。所需的输出是:
+------+-----------+----------+------------+------------+
| ID | firstname | lastname | startdate | identifier |
+------+-----------+----------+------------+------------+
| 43 | Bob | Smith | 2016-12-04 | 2 |
| 873 | Mary | Jones | 2018-05-01 | NULL |
| 120 | Bob | Smith | 2020-04-02 | 3 |
| 339 | Bob | Jones | 2019-03-01 | NULL |
| 1022 | Bob | Smith | 2015-11-21 | 1 |
+------+-----------+----------+------------+------------+
我想以任何方式查看数据;也就是说,无论我是搜索所有人,还是只搜索一条记录,我仍然可以看到 ID 120 在所有“Bob Smith”中排名第三。
我假设解决方案涉及使用窗口函数,但我不知道如何在 a 中使用它column_property
,或者实际上是否column_property
是将它添加到我的表的正确方法。这identifier
仅用于显示目的;我永远不会对它进行排序或查询,所以我确实只能在需要时生成它。但是,它仍然应该是 Person 对象的一部分,因此无论我到达那里,person.identifier
都会被填充。理想情况下,这不会有问题,所以如果我这样做SELECT * FROM person
,我就不会触发无数个单独的查询。
解决方案
您可以使用窗口函数。在纯 SQL 中:
select t.*,
case when count(*) over(partition by firstname, lastname) > 1
then row_number() over(partition by firstname, lastname order by startdate)
end as identifier
from mytable t
免责声明:我不知道如何在 SQL Alchemy 中表达这一点。
如果您需要一个WHERE
子句,则使用子查询(否则,该WHERE
子句也适用于窗口函数):
select *
from (
select t.*,
case when count(*) over(partition by firstname, lastname) > 1
then row_number() over(partition by firstname, lastname order by startdate)
end as identifier
from mytable t
) t
where id = 120
推荐阅读
- android - 列表中包含 Jetpack 中项目的适合/包裹宽度的任何想法 compose
- css - Blazor 问题,关于基于属性的宽度百分比
- java - 如何在java中打印一个arraylist并删除它的重复项
- java - 如何将绘图图上的 x 和 y 坐标转换为 GUI 上的像素
- authlib - 使用 authlib 设置知名 url 的任何示例?
- google-sheets - IF 和 CountIF 公式,你能改变重复单元格的第一个值吗?
- amazon-web-services - 键包含点时是否可以设置映射?
- python-3.x - Sqlalchemy 查询外键
- sql - BigQuery 谷歌分析自动化问题
- reactjs - 当我打印 PokemonData 时,它是未定义的