满足条件后，对所有记录和输出值进行计数[SQL]

Question

我想统计所有客户，并返回已注册的第三个客户的注册日期。

基本上评估注册的客户数量，一旦注册的客户数达到3，则返回注册日期和第三个客户的id

样品台

customer_id     signup_date   
3993            2019-01-01
9392            2019-01-02
2143            2019-01-03
8372            2019-01-04

输出表

customer_id   signup_date
2143          2019-01-03

Answer 1

使用row_number()过滤所需值：

row_number()→bigint
根据窗口分区中的行顺序，为每一行返回一个唯一的序列号，从一开始。

-- sample data
WITH dataset (customer_id, signup_date ) AS (
VALUES (3993, date '2019-01-01'),
(9392, date '2019-01-02'),
(2143, date '2019-01-03'),
(8372, date '2019-01-04')
) 
--query
select customer_id, signup_date
from (
select *, 
row_number() over(order by signup_date) rn
from dataset
)
where rn = 3

输出：

customer_id	signup_date
2143	2019-01-03

select * from (
select 
customer_id, 
signup_date, 
rank() over (order by signup_date) 
from signups
) sub where sub.rank = 3;

customer_id | signup_date | rank 
-------------+-------------+------
2143 | 2019-01-03  |    3
(1 row)

您可以使用

select customer_id, signup_date
from SampleTable
order by signup_date
offset 2
fetch next 1 rows

注册日期总是按升序排列吗？通常，当表有大量行时，Rank((函数会更好地工作。Rank((比Row_Number((快。我的建议是，只有在需要快速获得结果时才使用子查询。如果查询需要每天使用、重复使用，请尝试使用CTE以获得更好的可读性和性能。

With CTE_Customer_Signup_Date AS (
SELECT Rank() OVER (ORDER BY signup_date) AS Rank
, customer_id
, signup_date
FROM your_table
)
Select customer_id, signup_date from CTE_Customer_Signup_Date 
where Rank =3 

您可以使用这样的东西：

SELECT customer_id, signup_date
FROM (
SELECT ROW_NUMBER() OVER (ORDER BY signup_date) AS row_num
, customer_id
, signup_date
FROM your_table
) -- AS sub
WHERE row_num = 3

SELECT * FROM t
ORDER BY signup_date
LIMIT 1 OFFSET 4