3 ms·
Let’s start with an example to explain why it’s difficult to write in SQL: calculate the maximum consecutive days that a stock keeps rising. SELECT MAX(Continu
by followSPL 3y ago
Let’s start with an example to explain why it’s difficult to write in SQL: calculate the maximum consecutive days that a stock keeps rising.
SELECT MAX(ContinuousDays)
FROM (SELECT COUNT(*) ContinuousDays
FROM (SELECT SUM(UpDownTag) OVER ( ORDER BY TradeDate) NoRisingDays
FROM (SELECT TradeDate,
CASE WHEN Price>LAG(price) OVER ( ORDER BY TradeDate)
THEN 0 ELSE 1 END UpDownTag
FROM Stock ) )
GROUP BY NoRisingDays )
SQL adopts a four-layer nested code, which is more complicated overall. This problem was once used as a recruitment test of our company, the pass rate was less than 20%. Since this test question was too difficult, we modified it: give the statement and ask the candidate to find its objective. Unfortunately, the pass rate was still not high. What does it tell us? It tells us that once the calculation logic becomes slightly complex, SQL will become difficult in both understanding and writing!
Then, how SPL solve these?
For the first example:
Stock.sort(TradeDate).group@i(Price<Price[-1]).max(~.len())