具有窗口功能的column_property(或其他额外的列)? [英] column_property (or other extra column) with window function?

问题描述

在将SQLAlchemy与MySQL数据库一起使用时，我遇到以下情况.假设一个Person表看起来像这样:

I have the following situation, using SQLAlchemy with a MySQL database. Assume a Person table that looks like this:

+------+-----------+----------+------------+
|  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.所需的输出是:

I need to generate an extra column (or any equivalent solution) having the property: For anyone having the same firstname and lastname, return a sequence number ordered by the startdate. If a person doesn't share a firstname and lastname, return NULL. The desired output is:

+------+-----------+----------+------------+------------+
|  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是所有鲍勃·史密斯"中的#3.

I'd like to do this for any way of looking at the data; that is, whether I'm doing a search on all people, or just a single record, I can still see that ID 120 is #3 of all the "Bob Smith"s.

我认为该解决方案涉及使用窗口函数，但我无法弄清楚如何在 column_property 中使用此函数，或者实际上 column_property 是否是将其添加到我的表的正确方法.此 identifier 仅用于显示目的；我永远不会对它进行排序或查询，因此我的确只能在需要时生成它.但是，它仍然应该是Person对象的一部分，因此无论我到那里，都会填充 person.identifier .理想情况下，这不会出现问题，因此如果我执行 SELECT * FROM person ，就不会触发无数个单独的查询.

I assume that the solution involves using a window function, but I can't figure out how to use this in a column_property, or whether column_property is in fact the right way to add this to my table. This identifier would only be used for display purposes; I'd never be sorting or querying on it, so I could indeed only generate it when needed. However, it should still be part of the Person object, so that however I get there, person.identifier will be populated. And ideally this wouldn't be problematically slow, so that if I do SELECT * FROM person, I'm not firing off a zillion individual queries.

推荐答案

您可以使用窗口函数.在纯SQL中:

You can use window functions. In pure 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中表达这一点.

Disclaimer: I cannot tell how to express this in SQL Alchemy.

如果需要 WHERE 子句，请使用子查询(否则， WHERE 子句也适用于window函数):

If you need a WHERE clause, then use a subquery (otherwise, the WHERE clause applies to the window function as well):

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

这篇关于具有窗口功能的column_property(或其他额外的列)?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！