<> preface

Can you turn to this blog , We must have a certain understanding of the basic use of window function , There is no more nonsense here .

This blog post is mainly about , If you add where condition .

For the convenience of understanding , The following cases use the ranking function ：row_number() Let's talk about it . The same is true for other window functions , Let's draw inferences from one instance .

You can learn from this article ：

* use UNION ALL Divide and rule to solve the problem

* Use window function in a small skill to solve this problem （ Simple and efficient ）

<> case

If we want to rank a group of data according to a certain column , It is generally used row_number(), But if we want to go to a column that meets the criteria row_number() Ranking ,

for instance , There is a set of data ：

spark-sql (default)> select score from math_test; 30 50 101 300 456 768 999 130

350 1130 1350 1131 1150

We want to be right score Column greater than 300 Ranking based on the data of （ from 1 start ）, Less than or equal to 300 Data is filled with default values null.

<> Solution

*

UNION ALL Divide and rule

The idea of this scheme is very simple , That is to extract the qualified ones , adopt row_number() Ranking , Then fill in the default values for those that do not meet the criteria null, Finally, two results are given union

all get up .

For the convenience of seeing the effect , Right after processing score A sort was made （ This step can be removed , It's just a demonstration ）.

SELECT score,rk FROM ( SELECT score,ROW_NUMBER() OVER(ORDER BY score) AS rk

FROM MATH_TEST WHERE score > 300 UNION SELECT score,NULL AS rk FROM MATH_TEST

WHERE score <= 300 )T ORDER BY score -- give the result as follows ： score rk 30 NULL 50 NULL 101 NULL

130 NULL 300 NULL 350 1 456 2 768 3 999 4 1130 5 1131 6 1150 7 1350 8

UNION ALL It's a simple way , It's easy to understand , But it's still too long to write , And it's a bit wordy and inefficient , Need to scan 2 Sub table .

*

Window function tips

hey , So students in need can see this way , A little skill of using window function .

Here we put in the whole SQL

SELECT score ,CASE WHEN score > 300 THEN ROW_NUMBER() OVER(PARTITION BY (CASE

WHEN score > 300 THEN 1 ELSE 2 END) ORDER BY score) ELSE NULL END AS rk FROM

math_test-- give the result as follows ： score rk 350 1 456 2 768 3 999 4 1130 5 1131 6 1150 7 1350 8

30 NULL 50 NULL 101 NULL 130 NULL 300 NULL

Let's explain this way separately ：

CASE WHEN score > 300 THEN < Ranking processing > ELSE NULL END AS rk

At this level, we judge score > 300 We need to do ranking , Instead, fill in null

stay < Ranking processing >：

ROW_NUMBER() OVER(PARTITION BY (CASE WHEN score > 300 THEN 1 ELSE 2 END) ORDER

BY score)

We use PARTITION BY afferent CASE WHEN score > 300 THEN 1 ELSE 2 END, take score >

300 Set to group 1, Set to group instead 2.

In this way, we will be able to “ notice ” What's important is that score > 300 Under the condition of the data , Only right score > 300 Data usage of row_number() It's sorted .

*

If not in parition by How about going to the group ?

first , The result is this ：

SELECT score ,CASE WHEN score > 300 THEN ROW_NUMBER() OVER( ORDER BY score)

ELSE NULL END AS rk FROM math_test -- give the result as follows ： score rk 30 NULL 50 NULL 101 NULL

130 NULL 300 NULL 350 6 456 7 768 8 999 9 1130 10 1131 11 1150 12 1350 13

We see , Although it didn't <= 300 The data for is populated as the default null, however , Because the window function is able to see “ whole ” Data for , So in over(order by

score) after , The ranking results are not what we thought .

<> summary

In this paper, a ranking case is used to illustrate and explain how to add a function in the window where The conditions achieved our expected results , You can draw inferences from one instance .

Either way is OK , But I'm still keen on the second method , Simple and efficient .

That's what it says SQL It's wonderful ~~~~

– by Two monkeys

Technology

- Python95 blogs
- Java87 blogs
- Flow Chart79 blogs
- Vue46 blogs
- MySQL30 blogs
- programing language27 blogs
- Linux26 blogs
- algorithm22 blogs
- more...

Daily Recommendation

©2020 ioDraw All rights reserved

C# The name space of python To solve the problem of dictionary writing list in 【 skill 】 Simple solutions Mac Insufficient disk space use python turtle Drawing simple strokes C Language implementation of minesweeping game CLion Novice step on the pit ：CMake project is not loaded I wish you all the best （ element ） Happy Lantern Festival ! Let's guess lantern riddles sewer , Dead rat , faeces ...... The smell is dregs in front of it 2020 Qizhi Developer Conference , These open classes are not to be missed !Mac disk space “ second ” Lose weight , Just because of this step of God operation !