This article will show you how to group data, take out the first n sections of data for each data group.
Sample Data
Current data, as shown in the following table:
empno | ename | job | sal |
---|---|---|---|
7369 | SMITH | CLERK | 800.0 |
7876 | SMITH | CLERK | 1100.0 |
7900 | JAMES | CLERK | 950.0 |
7934 | MILLER | CLERK | 1300.0 |
7499 | ALLEN | SALESMAN | 1600.0 |
7654 | MARTIN | SALESMAN | 1250.0 |
7844 | TURNER | SALESMAN | 1500.0 |
7521 | WARD | SALESMAN | 1250.0 |
Implementation Method
You can achieve this in two ways:
-
Take out the line number of each data and filter it with the where statement.
SELECT * FROM ( SELECT empno , ename ,sal , job , ROW_NUMBER() OVER (PARTITION BY job ORDER BY sal) AS rn FROM emp ) tmp WHERE rn < 10;
-
Implement the Split Function using UDTF.
For more information, see the last example in this article. This example can more quickly judge the current sequence number. In this example, it will not work if you have exceeded the planned number of lines (for example, 10 ), and thus improve the efficiency of the calculation.