SQL Server 在SQL中查找列值随时间发生更改的所有行[已关闭]

b4qexyjb 于 2022-12-10 发布在其他

关注(0)|答案(1)|浏览(121)

Closed. This question needs details or clarity . It is not currently accepting answers.
Want to improve this question? Add details and clarify the problem by editing this post .

Closed 4 days ago.
Improve this question
So I have three columns: Time_id, accounts and code. See attached picture for a snippet of the data.
I want to find all account that have changed the code from 7100 to 7000 for that account, with ordered by the most recent ones.
Time_id: is the date generated once every month for each account with updated fields. In the format of yyyymmdd account: is an unique account id for this customer code: is a four letter digit describing

I have tried a LAG of 'code' over a partition by time_id. However this returned a LAG code of from a different account. Not sure how to transform the query to only return changed code from LAG based on the same accounts. This was the query I tried:

SELECT time_id, account, code
    ,LAG(code, 1) OVER (partition by time_id order by time_id) LAG_1
  FROM my_table
  group by time_id, account, code

I was hoping to get all the accounts and the rows where code went from 7100 to 7000 and when it happened. For instance I want to get account 12500 and 15500 returned from the table below with the row when it changed from 7100:
| time_id | account | code |
| ------------ | ------------ | ------------ |
| 20220510 | 12500 | 7100 |
| 20221101 | 12500 | 7000 |
| 20221120 | 12500 | 7000 |
| 20221201 | 17500 | 7100 |
| 20221202 | 12500 | 7100 |
| 20221203 | 15500 | 7100 |
| 20221204 | 15500 | 7000 |
| 20221205 | 15500 | 7000 |
I appreciate any new suggestions. Or improvements to my own query

sql-server

来源：https://stackoverflow.com/questions/74694886/find-all-rows-that-has-a-changed-column-value-over-time-in-sql

1条答案

按热度按时间

bfnvny8b1#

Recall that PARTITION BY is the rolling window equivalent of a GROUP BY . Since you want to look at the changes in codefor a particular account, you need to partition by account:

SELECT
        time_id,
        account,
        code,
        LAG(code) OVER (
          PARTITION BY account
          ORDER BY time_id
        ) AS previous_code
      FROM
        my_table

This query will return your original (time_id, account, code) records with a new column, previous_code , which denotes the value of code for the most recent preceding record for that account.
Now we can make progress:

WITH lagged_table AS (
    SELECT
        time_id,
        account,
        code,
        LAG(code) OVER (
          PARTITION BY account
          ORDER BY time_id
        ) AS previous_code
      FROM
        my_table
)

    SELECT
        time_id,
        account
      FROM
        lagged_table
     WHERE
           previous_code = 7100
       AND code = 7000

This should get you exactly what you want: an account ID and a time for all cases when the code switched from 7100 to 7000.

赞(0）回复(0）举报 2022-12-10

我来回答

SQL Server 在SQL中查找列值随时间发生更改的所有行[已关闭]

1条答案

相关问题

热门标签

最新问答