Shulou(Shulou.com)06/01 Report--

Today, the editor will take you to understand how SQL queries the records of rising N times in a row. The knowledge points in the article are introduced in great detail. Friends who feel helpful can browse the content of the article together with the editor, hoping to help more friends who want to solve this problem to find the answer to the problem. Let's follow the editor to learn more about "how to query the record of rising N times in a row" with the editor.

How to query the record of N consecutive increases with SQL?

This problem is very difficult to do with SQL!

The theoretical basis of SQL is disordered set, and there is no concept of order in early SQL, so it is very difficult to use JOIN to create order. Later, the window function was added to the SQL2003 standard, which alleviated the problem to some extent, but it is still not easy to solve the continuous rising problem.

For example: query stocks that have risen for n consecutive days. The existing database table stock data is as follows:

Codestockdateclose0000622015-01-058.910000622015-01-068.310000622015-01-077.60000622015-01-087.930000622015-01-097.72.

If you query stocks that have risen for three days in a row, write them in SQL as follows:

WITH An AS

(SELECT CODE,STOCKDATE, (CLOSE-LAG (CLOSE) OVER (PARTITION BY CODE ORDER BY STOCKDATE)) CLOSE_UP FROM STOCK)

B AS

(SELECT CODE

CASE WHEN CLOSE_UP > 0 AND

LAG (CLOSE_UP) OVER (PARTITION BY CODE ORDER BY STOCKDATE) > 0 AND

LAG (CLOSE_UP,2) OVER (PARTITION BY CODE ORDER BY STOCKDATE) > 0

THEN 1 ELSE 0 END UPDAYS3 FROM A)

SELECT DISTINCT CODE FROM B WHERE UPDAYS3=1

Here to write 3 days dead, to query 5 days in a row will be more complicated, n days do not know how to write.

For this ordered operation, it would be much easier to use the SPL language of the aggregator, with only two lines of code:

A1=connect ("mydb") .query ("select * from stock order by code, stockdate") .group @ o (code) 2=A1.select ((a=if (close > close [- 1], aplet0)) > = 5) > 0). (code)

If you want to query n days, just change 5 of A2 to n.

SPL is implemented based on ordered sets and supports ordered computing thoroughly. It can clearly express the records of any position in the set. Through absolute / relative position positioning, it is easy to calculate the daily rise and fall of stocks.

Thank you for your reading, the above is the whole content of "SQL how to query the record of rising N times in a row". Friends who learn to learn to hurry up to operate it. I believe that the editor will certainly bring you better quality articles. Thank you for your support to the website!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.