指定时间之前的 COUNT 次，自加入?

本文介绍了指定时间之前的 COUNT 次，自加入?的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

为简单起见，假设我有两个表:

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'

或类似的.

dbfiddle 演示