英文:
Continuous Flag based on presence on value in group - SQL
问题
我正在尝试编写一个查询，用于标记每个账户在4月22日的“从BD中删除”的状态。一旦发生这种情况，我希望该行以及每个账户的后续行都被标记。我可以编写一个针对4月22日的“从BD中删除”的情况语句，但这将无法捕获每个账户的后续行。
所需输出如下：
英文:
I am attempting to write a query that flags the status of 'Remove from BD' on April 22 per account. Once that occurs, I would for that row to be flagged along with any subsequent rows per account. I can write a case statement for 'Remove from BD' on April 22, but that will not capture the subsequent rows per account.
Sought output below:
答案1
得分: 1
以下是代码的中文翻译：
如果您希望将标志持久保存为实际列，可以考虑类似以下的方法：
UPDATE t1
SET flag = CASE WHEN t2.account IS NOT NULL THEN 'X' END
FROM test t1
LEFT OUTER JOIN test t2
ON t2.account = t1.account
AND t2.transactionpostdate <= t1.transactionpostdate
AND t2.status = 'Remove From BD'
AND t2.transactionpostdate = '2022-01-22';
这将将表连接到自身。如果没有具有相同“account”且<=“transactionpostdate”的列，则“t2.account”将为null。因此，“CASE WHEN t2.account IS NOT NULL THEN 'X' END”将为您提供所需的标志。您可以在[此fiddle中查看它的运行方式][1]。
尽管如此，我建议*不*将其持久保存在表中。它完全依赖于其他数据，因此它是多余的信息。您可以在查询中计算它，或者创建一个视图或计算列来计算它。例如，在[这里][2]，它被返回而不是存储（不浪费磁盘空间，您不会意外忘记更新列，未来可以轻松更改逻辑）：
SELECT test.*, CASE WHEN max(CASE WHEN status = 'Remove From BD' and transactionpostdate='2022-04-22' THEN 1 ELSE 0 END) OVER (PARTITION BY account ORDER BY transactionpostdate) = 1 THEN 'X' END flag
FROM test
ORDER BY account, transactionpostdate
[1]: https://dbfiddle.uk/gJFyxfjj
[2]: https://dbfiddle.uk/kWbqgaxk
英文:
Since you want to persist the flag as an actual column, you could consider something like:
UPDATE t1
SET flag = CASE WHEN t2.account IS NOT NULL THEN 'X' END
FROM test t1
LEFT OUTER JOIN test t2
ON t2.account = t1.account
AND t2.transactionpostdate <= t1.transactionpostdate
AND t2.status = 'Remove From BD'
AND t2.transactionpostdate = '2022-01-22'
This joins the table to itself. If there is no column for the same account with a <= transactionpostdate, then t2.account will be null. Therefore CASE WHEN t2.account IS NOT NULL THEN 'X' END gives you the flag you want. You can see it working in this fiddle.
Having said that, I would recommend not persisting this in the table. It depends entirely on other data, so it's redundant information. You could calculate it in your queries, or create a view or calculated column that calculates it. For example, here it is being returned without being stored (doesn't waste disk space, you can't accidentally forget to update the column, and the logic can be changed easily in the future):
SELECT test.*, CASE WHEN max(CASE WHEN status = 'Remove From BD' and transactionpostdate='2022-04-22' THEN 1 ELSE 0 END) OVER (PARTITION BY account ORDER BY transactionpostdate) = 1 THEN 'X' END flag
FROM test
ORDER BY account, transactionpostdate
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论