如何识别列值在MySQL中一段时间内是否减少?

Question

我想找出在过去十天内价格下跌的股票。例如，在下面的数据中，该股票的价值在过去十天中下降了。

我想强调这类股票。我想知道如何写一个查询。

我使用MySQL 8+。任何帮助都是非常感激的。提前谢谢。

的问候Vadi

ID   SECURITY   MARKET_PRICE     EFFECTIVE_DATE
1    stockname  10.0000000000    2021-01-01
2    stockname  9.0000000000     2021-01-02
3    stockname  8.0000000000     2021-01-03
4    stockname  7.0000000000     2021-01-04
5    stockname  6.0000000000     2021-01-05
6    stockname  5.0000000000     2021-01-06
7    stockname  4.0000000000     2021-01-07
8    stockname  3.0000000000     2021-01-08
9    stockname  2.0000000000     2021-01-09
10   stockname  1.0000000000     2021-01-10

Answer 1

对于您明显的需要，下面的内容就足够了…

WITH 
cte1 AS ( SELECT *, LAG(price) OVER (PARTITION BY market 
ORDER BY date) <= price AS not_decreased
FROM table ),
cte2 AS ( SELECT *, SUM(decreased) OVER (PARTITION BY market 
ORDER BY date 
RANGE BETWEEN INTERVAL 10 DAY PRECEDING 
AND CURRENT ROW) AS not_decreased10days
FROM cte1 )
SELECT * 
FROM cte2 
WHERE not_decreased10days = 0

Answer 2

我也遇到过下面的查询。如果有人感兴趣的话，这似乎也可以正常工作。

SELECT  MPTEST.*         
,IF(@M_PRICE >= MARKET_PRICE, 'True', 'False') As IS_VALID
,(@M_PRICE:=MARKET_PRICE) as M_PRICE
FROM (    
SELECT * FROM market_price_test WHERE EFFECTIVE_DATE <= '2021-01-10'
ORDER BY EFFECTIVE_DATE DESC LIMIT 10
) MPTEST,(SELECT @M_PRICE:= 
(SELECT MARKET_PRICE FROM market_price_test WHERE EFFECTIVE_DATE <= '2021-01-10'
ORDER BY EFFECTIVE_DATE DESC LIMIT 1)
) MPTEST1
ORDER BY SECURITY, EFFECTIVE_DATE;