问题描述
我有一个客户会话表，它具有会话id和会话日期以及其他列。我想看看每个客户最近的3次会议。
提供了以下用于创建数据的示例SQL:
提供了以下用于创建数据的示例SQL:
CREATE TABLE Customer (
CustomerID int,
Name varchar(255),
SessionDate Date,
Sessionid int,
Result varchar(255)
);INSERT INTO Customer VALUES (123, 'Ram', TO_DATE('01.01.1991','DD.MM.YYYY'), 1, 'Pass');
INSERT INTO Customer VALUES (123, 'Ram', TO_DATE('02.01.1991','DD.MM.YYYY'), 2, 'Fail');
INSERT INTO Customer VALUES (123, 'Ram', TO_DATE('03.01.1991','DD.MM.YYYY'), 3, 'Fail');
INSERT INTO Customer VALUES (123, 'Ram', TO_DATE('04.01.1991','DD.MM.YYYY'), 4, 'Pass');
INSERT INTO Customer VALUES (456, 'John', TO_DATE('05.01.1991','DD.MM.YYYY'), 5, 'Pass');
INSERT INTO Customer VALUES (789, 'Snow', TO_DATE('06.01.1991','DD.MM.YYYY'), 6, 'Pass');
INSERT INTO Customer VALUES (147, 'Denarys', TO_DATE('07.01.1991','DD.MM.YYYY'), 7, 'Pass');
INSERT INTO Customer VALUES (258, 'Targarian', TO_DATE('08.01.1991','DD.MM.YYYY'), 8, 'Pass');
INSERT INTO Customer VALUES (369, 'Little', TO_DATE('09.01.1991','DD.MM.YYYY'), 9, 'Pass');
INSERT INTO Customer VALUES (369, 'Little', TO_DATE('10.01.1991','DD.MM.YYYY'), 25, 'Fail');
INSERT INTO Customer VALUES (369, 'Little', TO_DATE('11.01.1991','DD.MM.YYYY'), 26, 'Fail');
INSERT INTO Customer VALUES (369, 'Little', TO_DATE('01.01.1992','DD.MM.YYYY'), 27, 'Fail');
INSERT INTO Customer VALUES (369, 'Little', TO_DATE('01.01.1993','DD.MM.YYYY'), 28, 'Fail');
INSERT INTO Customer VALUES (369, 'Little', TO_DATE('01.01.1994','DD.MM.YYYY'), 29, 'Fail');
INSERT INTO Customer VALUES (963, 'Finger', TO_DATE('01.01.1995','DD.MM.YYYY'), 10, 'Pass');
INSERT INTO Customer VALUES (852, 'Dragon', TO_DATE('01.01.1996','DD.MM.YYYY'), 11, 'Pass');
INSERT INTO Customer VALUES (741, 'Glass', TO_DATE('01.01.1997','DD.MM.YYYY'), 12, 'Pass');
INSERT INTO Customer VALUES (159, 'Aarya', TO_DATE('01.01.1998','DD.MM.YYYY'), 13, 'Pass');
INSERT INTO Customer VALUES (357, 'Stark', TO_DATE('01.01.1999','DD.MM.YYYY'), 14, 'Fail');
INSERT INTO Customer VALUES (745, 'Lan', TO_DATE('01.01.1995','DD.MM.YYYY'), 15, 'Pass');
INSERT INTO Customer VALUES (965, 'Ister', TO_DATE('01.01.1999','DD.MM.YYYY'), 16, 'Pass');
INSERT INTO Customer VALUES (145, 'White', TO_DATE('01.01.1891','DD.MM.YYYY'), 17, 'Fail');
INSERT INTO Customer VALUES (236, 'Random', TO_DATE('01.01.1791','DD.MM.YYYY'), 18, 'Pass');
INSERT INTO Customer VALUES (528, 'Hero', TO_DATE('01.01.1981','DD.MM.YYYY'), 19, 'Pass');
INSERT INTO Customer VALUES (417, 'Iron', TO_DATE('01.01.1901','DD.MM.YYYY'), 20, 'Fail');
INSERT INTO Customer VALUES (639, 'Thanos', TO_DATE('01.01.1951','DD.MM.YYYY'), 21, 'Pass');
INSERT INTO Customer VALUES (471, 'Cersi', TO_DATE('01.01.1921','DD.MM.YYYY'), 22, 'Pass');
INSERT INTO Customer VALUES (582, 'Thor', TO_DATE('01.01.1901','DD.MM.YYYY'), 23, 'Pass');
INSERT INTO Customer VALUES (693, 'Kalisi', TO_DATE('01.01.1961','DD.MM.YYYY'), 24, 'Pass'); 专家解答
当人们给我们测试数据时，我非常喜欢它!谢谢!
关于分析的完整教程
SQL> CREATE TABLE Customer (
2 CustomerID int,
3 Name varchar(12),
4 SessionDate Date,
5 Sessionid int,
6 Result varchar(10)
7 );
Table created.
SQL>
SQL> INSERT INTO Customer VALUES (123, 'Ram', TO_DATE('01.01.1991','DD.MM.YYYY'), 1, 'Pass');
1 row created.
SQL> INSERT INTO Customer VALUES (123, 'Ram', TO_DATE('02.01.1991','DD.MM.YYYY'), 2, 'Fail');
1 row created.
SQL> INSERT INTO Customer VALUES (123, 'Ram', TO_DATE('03.01.1991','DD.MM.YYYY'), 3, 'Fail');
1 row created.
SQL> INSERT INTO Customer VALUES (123, 'Ram', TO_DATE('04.01.1991','DD.MM.YYYY'), 4, 'Pass');
1 row created.
SQL> INSERT INTO Customer VALUES (456, 'John', TO_DATE('05.01.1991','DD.MM.YYYY'), 5, 'Pass');
1 row created.
SQL> INSERT INTO Customer VALUES (789, 'Snow', TO_DATE('06.01.1991','DD.MM.YYYY'), 6, 'Pass');
1 row created.
SQL> INSERT INTO Customer VALUES (147, 'Denarys', TO_DATE('07.01.1991','DD.MM.YYYY'), 7, 'Pass');
1 row created.
SQL> INSERT INTO Customer VALUES (258, 'Targarian', TO_DATE('08.01.1991','DD.MM.YYYY'), 8, 'Pass');
1 row created.
SQL> INSERT INTO Customer VALUES (369, 'Little', TO_DATE('09.01.1991','DD.MM.YYYY'), 9, 'Pass');
1 row created.
SQL> INSERT INTO Customer VALUES (369, 'Little', TO_DATE('10.01.1991','DD.MM.YYYY'), 25, 'Fail');
1 row created.
SQL> INSERT INTO Customer VALUES (369, 'Little', TO_DATE('11.01.1991','DD.MM.YYYY'), 26, 'Fail');
1 row created.
SQL> INSERT INTO Customer VALUES (369, 'Little', TO_DATE('01.01.1992','DD.MM.YYYY'), 27, 'Fail');
1 row created.
SQL> INSERT INTO Customer VALUES (369, 'Little', TO_DATE('01.01.1993','DD.MM.YYYY'), 28, 'Fail');
1 row created.
SQL> INSERT INTO Customer VALUES (369, 'Little', TO_DATE('01.01.1994','DD.MM.YYYY'), 29, 'Fail');
1 row created.
SQL> INSERT INTO Customer VALUES (963, 'Finger', TO_DATE('01.01.1995','DD.MM.YYYY'), 10, 'Pass');
1 row created.
SQL> INSERT INTO Customer VALUES (852, 'Dragon', TO_DATE('01.01.1996','DD.MM.YYYY'), 11, 'Pass');
1 row created.
SQL> INSERT INTO Customer VALUES (741, 'Glass', TO_DATE('01.01.1997','DD.MM.YYYY'), 12, 'Pass');
1 row created.
SQL> INSERT INTO Customer VALUES (159, 'Aarya', TO_DATE('01.01.1998','DD.MM.YYYY'), 13, 'Pass');
1 row created.
SQL> INSERT INTO Customer VALUES (357, 'Stark', TO_DATE('01.01.1999','DD.MM.YYYY'), 14, 'Fail');
1 row created.
SQL> INSERT INTO Customer VALUES (745, 'Lan', TO_DATE('01.01.1995','DD.MM.YYYY'), 15, 'Pass');
1 row created.
SQL> INSERT INTO Customer VALUES (965, 'Ister', TO_DATE('01.01.1999','DD.MM.YYYY'), 16, 'Pass');
1 row created.
SQL> INSERT INTO Customer VALUES (145, 'White', TO_DATE('01.01.1891','DD.MM.YYYY'), 17, 'Fail');
1 row created.
SQL> INSERT INTO Customer VALUES (236, 'Random', TO_DATE('01.01.1791','DD.MM.YYYY'), 18, 'Pass');
1 row created.
SQL> INSERT INTO Customer VALUES (528, 'Hero', TO_DATE('01.01.1981','DD.MM.YYYY'), 19, 'Pass');
1 row created.
SQL> INSERT INTO Customer VALUES (417, 'Iron', TO_DATE('01.01.1901','DD.MM.YYYY'), 20, 'Fail');
1 row created.
SQL> INSERT INTO Customer VALUES (639, 'Thanos', TO_DATE('01.01.1951','DD.MM.YYYY'), 21, 'Pass');
1 row created.
SQL> INSERT INTO Customer VALUES (471, 'Cersi', TO_DATE('01.01.1921','DD.MM.YYYY'), 22, 'Pass');
1 row created.
SQL> INSERT INTO Customer VALUES (582, 'Thor', TO_DATE('01.01.1901','DD.MM.YYYY'), 23, 'Pass');
1 row created.
SQL> INSERT INTO Customer VALUES (693, 'Kalisi', TO_DATE('01.01.1961','DD.MM.YYYY'), 24, 'Pass');
1 row created.
SQL>
SQL>
SQL> select *
2 from (
3 select c.*, row_number() over ( partition by CustomerID order by sessiondate desc ) as seq
4 from customer c
5 )
6 where seq <= 3;
CUSTOMERID NAME SESSIONDA SESSIONID RESULT SEQ
---------- ------------ --------- ---------- ---------- ----------
123 Ram 04-JAN-91 4 Pass 1
123 Ram 03-JAN-91 3 Fail 2
123 Ram 02-JAN-91 2 Fail 3
145 White 01-JAN-91 17 Fail 1
147 Denarys 07-JAN-91 7 Pass 1
159 Aarya 01-JAN-98 13 Pass 1
236 Random 01-JAN-91 18 Pass 1
258 Targarian 08-JAN-91 8 Pass 1
357 Stark 01-JAN-99 14 Fail 1
369 Little 01-JAN-94 29 Fail 1
369 Little 01-JAN-93 28 Fail 2
369 Little 01-JAN-92 27 Fail 3
417 Iron 01-JAN-01 20 Fail 1
456 John 05-JAN-91 5 Pass 1
471 Cersi 01-JAN-21 22 Pass 1
528 Hero 01-JAN-81 19 Pass 1
582 Thor 01-JAN-01 23 Pass 1
639 Thanos 01-JAN-51 21 Pass 1
693 Kalisi 01-JAN-61 24 Pass 1
741 Glass 01-JAN-97 12 Pass 1
745 Lan 01-JAN-95 15 Pass 1
789 Snow 06-JAN-91 6 Pass 1
852 Dragon 01-JAN-96 11 Pass 1
963 Finger 01-JAN-95 10 Pass 1
965 Ister 01-JAN-99 16 Pass 1
25 rows selected.
SQL>
SQL>
关于分析的完整教程
文章转载自ASKTOM，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
