창 기능이있는 column_property (또는 기타 추가 열)?

Nov 15 2020

MySQL 데이터베이스와 함께 SQLAlchemy를 사용하는 다음 상황이 있습니다. 다음과 같은 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"중 # 3임을 알 수 있습니다.

솔루션에 창 함수 사용이 포함되어 있다고 가정하지만에서 이것을 사용하는 방법 column_property또는 column_property실제로 이것을 내 테이블에 추가하는 올바른 방법 인지 여부 를 알 수 없습니다 . 이것은 identifier표시 목적으로 만 사용됩니다. 나는 그것을 정렬하거나 쿼리하지 않았으므로 실제로 필요할 때만 생성 할 수 있습니다. 그러나 그것은 여전히 ​​Person 객체의 일부 여야하므로 내가 거기에 도착 person.identifier하더라도 채워질 것입니다. 그리고 이상적으로 이것은 문제가 될 정도로 느리지 않을 것입니다. 그래서 만약 내가 그렇게한다면 SELECT * FROM person, 수백만 개의 개별 쿼리를 발생시키지 않을 것입니다.

답변

4 GMB Nov 15 2020 at 14:35

창 기능을 사용할 수 있습니다. 순수 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