英文:
query pre data by filter condition
问题
| total_assets | day_yield | create_date | create_date |
|---|---|---|---|
| 100 | 0.01 | 20171026 | null |
| 200 | -0.01 | 20171027 | 100 |
| 300 | -0.01 | 20171028 | 100 |
| 400 | -0.01 | 20171029 | 100 |
当 create_date='20171029' 时，找到 day_yield > 0 的前一条数据，结果为 100。
英文:
| total_assets | day_yield | create_date |
|---|---|---|
| 100 | 0.01 | 20171026 |
| 200 | -0.01 | 20171027 |
| 300 | -0.01 | 20171028 |
| 400 | -0.01 | 20171029 |
expect result:
| total_assets | day_yield | create_date | create_date |
|---|---|---|---|
| 100 | 0.01 | 20171026 | null |
| 200 | -0.01 | 20171027 | 100 |
| 300 | -0.01 | 20171028 | 100 |
| 400 | -0.01 | 20171029 | 100 |
when create_date='20171029' then find pre data of day_yiel>0,the result 100
答案1
得分: 0
使用LAG和CASE表达式来查找最近的正值（忽略NULL值）：
SELECT t.*,
LAG(CASE WHEN day_yield > 0 THEN total_assets END)
IGNORE NULLS OVER (ORDER BY create_date)
AS last_yield
FROM table_name t
对于示例数据：
CREATE TABLE table_name(total_assets, day_yield, create_date) AS
SELECT 100, +0.01, DATE '2017-10-26' FROM DUAL UNION ALL
SELECT 200, -0.01, DATE '2017-10-27' FROM DUAL UNION ALL
SELECT 300, -0.01, DATE '2017-10-28' FROM DUAL UNION ALL
SELECT 400, -0.01, DATE '2017-10-29' FROM DUAL;
输出：
| TOTAL_ASSETS | DAY_YIELD | CREATE_DATE | LAST_YIELD |
|---|---|---|---|
| 100 | 0.01 | 2017-10-26 00:00:00 | null |
| 200 | -0.01 | 2017-10-27 00:00:00 | 100 |
| 300 | -0.01 | 2017-10-28 00:00:00 | 100 |
| 400 | -0.01 | 2017-10-29 00:00:00 | 100 |
英文:
Use LAG with a CASE expression to find the most recent positive value (ignoring NULL values):
SELECT t.*,
LAG(CASE WHEN day_yield > 0 THEN total_assets END)
IGNORE NULLS OVER (ORDER BY create_date)
AS last_yield
FROM table_name t
Which, for the sample data:
CREATE TABLE table_name(total_assets, day_yield, create_date) AS
SELECT 100, +0.01, DATE '2017-10-26' FROM DUAL UNION ALL
SELECT 200, -0.01, DATE '2017-10-27' FROM DUAL UNION ALL
SELECT 300, -0.01, DATE '2017-10-28' FROM DUAL UNION ALL
SELECT 400, -0.01, DATE '2017-10-29' FROM DUAL;
Outputs:
| TOTAL_ASSETS | DAY_YIELD | CREATE_DATE | LAST_YIELD |
|---|---|---|---|
| 100 | .01 | 2017-10-26 00:00:00 | null |
| 200 | -.01 | 2017-10-27 00:00:00 | 100 |
| 300 | -.01 | 2017-10-28 00:00:00 | 100 |
| 400 | -.01 | 2017-10-29 00:00:00 | 100 |
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论