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

This article mainly analyzes the relevant knowledge points of how to calculate the median of each packet by SQL. The content is detailed and easy to understand, and the operation details are reasonable, which has a certain reference value. If you are interested, you might as well follow the editor to have a look, and follow the editor to learn more about how SQL calculates the median of each group.

The median is the data value that is in the middle after a set of data is sorted. If the number of data is odd, the median is the value in the middle; if it is even, it is the average of the two numbers in the middle.

How to query the median of each group after the data packet?

It is very difficult to solve this problem with SQL!

The collection of SQL is unordered, there is no concept of data location, and it is necessary to artificially create line numbers, but it is also difficult to number each group independently. Later, a window function was added to the SQL2003 standard to number the groups, but finding the median of each group is still tedious.

For example: the SCORES data of the existing transcript are as follows, which requires that the median score of each subject be found.

COURSESCOREHistory68.5History79.0History82.5History88.0History93.5Maths75.5Maths83.0Maths85.0Maths95.5

The median score of each subject should be:

COURSESCOREHistory82.5Maths84.0

Take Oracle, for example, and write it in SQL as follows:

WITH An AS

(SELECT COURSE, SCORE

ROW_NUMBER () OVER (PARTITION BY COURSE ORDER BY SCORE) AS RN

COUNT (*) OVER (PARTITION BY COURSE) AS CNT

FROM SCORES)

B AS

(SELECT * FROM A WHERE RN > (CNT-0.5) / 2 AND RN

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.