指定时间之前的 COUNT 次,自加入? [英] COUNT number of times prior to specified time, self-join?
问题描述
为简单起见,假设我有两个表:
For simplicity let's assume I have two tables:
billing
具有以下字段 bill_id
(primary, unique key, int), person_id
, bill_date
,和billing_detail
包含 bill_id
和 service_type
.
billing
with the following fields bill_id
(primary, unique key, int), person_id
, bill_date
, and
billing_detail
containing bill_id
and service_type
.
首先,我想要一个客户列表(person_id
),他们在给定时间段内为给定范围的服务付费,所以很简单
First of all I would like a list of clients (person_id
) who were billed for a given range of services in a given period, so simply
SELECT billing.person_id, billing.bill_date
FROM billing
INNER JOIN billing_detail ON billing.bill_id = billing_detail.bill_id
WHERE billing_detail.service_type IN (list of services)
AND billing.bill_date between some_date and another_date
我现在想要做的是还显示给定客户在给定日期之前为相同范围的服务收费的次数.
What I would like to do now is to also show how many times a given client was billed for the same range of services PRIOR to the given date.
假设 billing
表包含:
1 | 1 | 1/1/2020
2 | 1 | 1/2/2020
3 | 1 | 1/3/2020
4 | 1 | 1/4/2020
4 | 1 | 2/4/2020
billing_detail
表包含:
1 | A
2 | B
3 | A
4 | B
5 | A
因此,如果我要为客户 1 生成服务类型 A 的 1 月至 4 月期间的报告,预期结果将是
So if I was to run a report for client 1 for the period from January to April for service type A, the expected outcome would be
1 | 1/1/2020 | 0 (no A type service prior to this date)
1 | 1/3/2020 | 1 (One A type service prior to this date)
1 | 2/4/2020 | 2 (Two A type services prior ot this date).
这可能涉及两种表上的某种自联接,但我疲惫的鸟脑目前似乎无法想出答案.任何帮助将不胜感激.
This probably involves some kind of self-join on both tables but my tired bird-brain can't seem to come up with the answer at the moment. Any help would be appreciated.
推荐答案
您可以在 person_id
和 service_type
之上使用 ROW_NUMBER()
得到你想要的结果,减去一以从 0 开始值:
You can use ROW_NUMBER()
over the person_id
and service_type
to get the result you want, subtracting one to start the values at 0:
SELECT person_id, bill_date, service_type,
ROW_NUMBER() OVER (PARTITION BY person_id, service_type ORDER BY bill_date) - 1 AS prior_services
FROM billing b
JOIN billing_detail bd ON bd.bill_id = b.bill_id
这将为您提供所有服务类型的数据:
This will give you data for all service types:
person_id bill_date service_type prior_services
1 2020-01-01 A 0
1 2020-01-03 A 1
1 2020-02-04 A 2
1 2020-01-02 B 0
1 2020-01-04 B 1
要限制服务类型,请添加
To restrict the service type, add a
WHERE service_type = 'A'
或类似的.
这篇关于指定时间之前的 COUNT 次,自加入?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!