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

In SQL query, grouping according to column An and performing statistical functions for column B after grouping is a common and important function, such as

Select T.a, max (T.b) from T order by T.an asc, T.b desc group by T.a

Select T.a, count (T.b) from T order by T.an asc, T.b desc group by T.a

Keep writing SQL like this, until one day, a friend doing stocks (non-IT personnel) chatted with me, saying that he used Python to complete the data, wanted to analyze the top three stocks in each section, and asked me if I knew how to use SQL to find out (this is a group ranking to take a number of first-tier requirements). To be honest, this problem is very common, but I was a little confused all of a sudden, and I kept thinking about it on the way back. When I used SQLServer a few years ago, there was a grouping method ROW_NUMBER () OVER (), which seemed to realize this grouping ranking function.

Syntax format: row_number () over (partition by grouping column order by sort desc)

Instead, I searched the realization of MySQL on the Internet, and sure enough, a bunch of friends recorded it. With reference to a CSDN Weibo article "Mysql implements ROW_NUMBER () OVER ()", the implementation process is recorded as follows.

First, take a look at the following table structure:

View_market_trade_amt has three columns, the first column is the index, the second column is the stock symbol, and the third column is trading volume.

The demand is to find out the top three stocks traded in each index sector.

Before realizing MySQL rownum () over (), we need to understand the idea of this implementation: after sorting according to the grouping column, define the rownum tag, the mark of rownum changes with the change of the grouping column, when the value of the grouping column is the same, the rownum does not change, and finally each grouping column forms a rownum sequence, and then the ranking is filtered according to the rownum sequence.

Define rownumselect v.stockCodeDie v.tradeIndexjie v.amount vardef if (@ grpvar=v.tradeIndex,@rownum:=@rownum+1,@rownum:=1) as rownum, @ grpvar:=v.tradeIndexfrom (select stockCode,tradeIndex,amount from view_market_trade_amt order by tradeIndex asc, amount desc) v, (select @ grpvar:= null, @ rownum:=0) vardef

Vardef defines grouping variables and rownum variables, and assigns null and 0 by default, respectively

Grpvar assigns tradexIndex and determines whether the tradeIndex of the current record is equal to the variable, and if so, rownum+1, otherwise the assignment starts at 1.

Query results:

It can be seen that the corresponding rownum sequences of different tradeIndex are independent.

Filter records based on rownum

This step is much simpler. Based on the continuation above, filter out the ones with rownum less than 3. Select tradeIndex, stockCode, amount from (select v.stockCodeJournal v.TradeIndex) as resultwhere result.rownum if (@ grpvar=v.tradeIndex,@rownum:=@rownum+1,@rownum:=1) as rownum, @ grpvar:=v.tradeIndexfrom (select stockCode,tradeIndex,amount from view_market_trade_amt order by tradeIndex asc, amount desc) v, (select @ grpvar:= null, @ rownum:=0) vardef) as resultwhere result.rownum

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.