如果没有记录，则为第一个可用日期的值

Question

i have Items table:

item_id  |  date       | item_price |
---------+-------------+--------------
1        | 2022-12-05  |     15     |
2        | 2022-02-14  |     12     |
1        | 2022-11-12  |     50     |
4        | 2022-01-21  |     13     |
1        | 2021-12-12  |     10     |
6        | 2021-12-27  |     83     |

我用来从今天的日期中选择一周前的价格的查询:

SELECT 
items.item_id AS id,
items.item_price AS weekAgoPrice,
items2.item_price AS monthAgoPrice,
FROM
items
LEFT JOIN
items items2 ON items2.item_id = items.item_id
AND items2.date = DATE_SUB(CURDATE(), INTERVAL 30 DAY)
WHERE
items.item_id = '1'
AND items.date = DATE_SUB(CURDATE(), INTERVAL 7 DAY);

如果没有特定日期的条目，我如何修改将返回第一个可用日期的价格的查询。那些，如果指定的item_id没有7天前的价格，那么它应该返回6天前的值，如果不是6则是5。另外，如果1个月前没有价格，它应该返回29天前的值等。

Answer 1

您可以尝试使用max窗口函数:

With last_prices As
(
Select *, 
Max(Case 
When date Between DATE_SUB(CURDATE(), INTERVAL 7 DAY) And CURDATE() 
Then date 
End) Over (Partition By item_id) As last_price_date_week,
Max(Case 
When date Between DATE_SUB(CURDATE(), INTERVAL 1 MONTH) And CURDATE() 
Then date 
End) Over (Partition By item_id) As last_price_date_month
From items
)
Select item_id, date, item_price, 'last week price' As Price_Type
From last_prices
Where item_id = 1 And date = last_price_date_week
Union All
Select item_id, date, item_price, 'last month price' As Price_Type
From last_prices
Where item_id = 1 And date = last_price_date_month

看演示。

如果你愿意使用存储过程，你可以让这个任务更动态，看看这个过程:

Create Procedure GetLatesPrice(id INT, period INT, interval_type Varchar(1)) 
Begin
Select item_id, date, item_price
From
(
Select *, 
Case 
When interval_type='d' Then
Max(
Case 
When date Between DATE_SUB(CURDATE(), INTERVAL period Day) And CURDATE() 
Then date 
End
) Over (Partition By item_id)
When interval_type='m' Then
Max(
Case 
When date Between DATE_SUB(CURDATE(), INTERVAL period Month) And CURDATE() 
Then date 
End
) Over (Partition By item_id)
When interval_type='d' Then
Max(
Case 
When date Between DATE_SUB(CURDATE(), INTERVAL period Year) And CURDATE() 
Then date 
End
) Over (Partition By item_id)
End As last_price_date
From items
) T
Where date = last_price_date And item_id=id;
END  

然后执行这个过程，例如item_id= 1,15 days ago:

Call GetLatesPrice(1, 15, 'd');
-- d for days, m for months, y for years

看演示。

Answer 2

如果没有特定日期的条目，则可以使用COALESCE函数

SELECT 
items.item_id AS id,
COALESCE(
items.item_price,
(SELECT items.item_price FROM items WHERE items.item_id = '1' AND items.date = DATE_SUB(CURDATE(), INTERVAL 6 DAY)),
(SELECT items.item_price FROM items WHERE items.item_id = '1' AND items.date = DATE_SUB(CURDATE(), INTERVAL 5 DAY)),
...
) AS price
FROM items
WHERE items.item_id = '1'

Answer 3

SELECT物品。item_id AS id;合并(items.item_price,(选择项。item_price FROM items WHERE items。item_id = '1' AND items。date = DATE_SUB(CURDATE()， INTERVAL 6天))，(选择项。item_price FROM items WHERE items。item_id = '1' AND items。date = DATE_SUB(CURDATE()， INTERVAL 5 DAY))，…AS价格从项目物品的地方。Item_id = '1'

Answer 4

如果你需要的是标量值，那么这样就足够了:

SET @ItemID = 1;
SELECT (SELECT item_price FROM items WHERE item_id = @ItemID 
AND date1 >= DATE_ADD(CURDATE(), INTERVAL -7 DAY) order by date1 LIMIT 1) as WeekAgoPrice,
etc.

请参阅LATERAL文档。

如果没有示例输出数据，很难判断您的意图。