计算'with'中的每个分组值（Oracle SQL）

I'm building a query that is counting certain subgroups using several with statements. The final subquery that I'm having trouble with is the summation of the total amount of possible customers. I'm creating a sample that's a little more simplistic than what I'm really building, so bear with the sample.

我正在构建一个使用多个with语句计算某些子组的查询。我遇到问题的最终子查询是可能客户总数的总和。我正在创建一个比我真正构建的更简单的样本，所以请耐心一下。

Basically the end result should look like this:

基本上最终结果应如下所示：

Note that it isn't as simple as just adding the two columns together to get the total. In this case, the customers can, for example, leave the organization and then they wouldn't be counted in the total possible customers. There is a column that indicates when a customer enters that pool and another where the customer leaves. So I need something to do a count on each given month that takes that into consideration. (Basically a select count(*) from customers where entry_date < [month being evaluated] and leave date !< [month being evaluated]). This is where my problem comes in, so this is what I have so far:

请注意，它并不像将两列一起添加以获得总数那么简单。在这种情况下，客户可以，例如，离开组织，然后他们将不计入可能的总客户。有一列指示客户何时进入该池，另一列指示客户离开的位置。所以我需要在每个给定的月份做一些事情，并考虑到这一点。（基本上来自客户的选择计数（*），其中entry_date <[正在评估的月份]并且离开日期！<[正在评估的月份]）。这是我的问题所在，所以这是我到目前为止：

WITH customer_type1 AS 
(
    SELECT c1_visit_date, COUNT(*) AS c1_count
    FROM (SELECT TO_CHAR(c.visit_time, 'MON YYYY') AS c1_visit_date
    FROM customer c
    where customer_type='1')
    GROUP BY c1_visit_date;
),
customer_type2 AS 
(
    SELECT c2_visit_date, COUNT(*) as c2_count    
    FROM (SELECT TO_CHAR(c.visit_time, 'MON YYYY') AS c2_visit_date
    FROM customer c
    where customer_type='2')
    GROUP BY c2_visit_date;
),
all_possible_customers AS
(
    SELECT TO_DATE(customer_type1.c1_visit_date) AS APC_Date,
    (
        SELECT DISTINCT COUNT(c.customer_id)
        FROM customer c
        WHERE customer.start_date < APC_Date
        AND (customer.end_date = NULL OR customer.end_date > APC_Date)
    ) AS all_possible_customer_count
    FROM customer_type1;
)

SELECT customer_type1.c1_visit_date, customer_type1.c1_count, customer_type2.c2_count, all_possible_customers.all_possible_customer_count
FROM customer_type1
INNER JOIN customer_type1 ON customer_type1.c1_visit_date = customer_type2.c2_visit_date
INNER JOIN cusomter_type1 ON customer_type1.c1_visit_date = all_possible_customers.APC date;

But I keep running into syntax errors on the third with statement. (When I clean it up and run just the first two with statements that get me C.Type1_visit and C.Type2_Visit, they work fine.

但是我继续在第三个语句中遇到语法错误。（当我清理它并运行前两个语句时，我会使用C.Type1_visit和C.Type2_Visit，它们工作正常。

What am I missing in this third statement that would allow me to group the select statement by the first column?

我在第三个语句中遗漏了什么，这个语句允许我按第一列对select语句进行分组？

1 个解决方案

#1

From the query you've posted, I think this is what you're after:

从您发布的查询中，我认为这就是您所追求的：

SELECT TRUNC(visit_date, 'mm') visit_date,
       COUNT(CASE WHEN customer_type = '1' THEN 1 END) c1_count,
       COUNT(CASE WHEN customer_type = '2' THEN 1 END) c2_count,
       COUNT(CASE WHEN start_date < TRUNC(visit_date, 'mm') AND (end_date is null or end_date > TRUNC(visit_date, 'mm')) THEN 1 end) all_possible_customer_count
FROM   customer
GROUP BY TRUNC(visit_date, 'mm');

However, without sample input and expected output data to test with, it's hard to say whether that's anywhere near what you're after.

但是，如果没有样本输入和预期的输出数据进行测试，很难说这是否接近你所追求的。

I'm a little curious about the logic around your "all possible customer count" - can a customer have a visit date that's outside of the cusutomer's start and end dates? Also, what if the customer's start and end dates were, say, 2nd - 20th March 2017? They wouldn't be counted in the all_possible_customer_count as 1st March is not inside that range.

我对“所有可能的客户数量”的逻辑有点好奇 - 客户的访问日期是否超出了客户的开始和结束日期？此外，如果客户的开始和结束日期是2017年3月2日至20日，该怎么办？它们不会计入all_possible_customer_count，因为3月1日不在该范围内。

ETA, upon thinking about it, I'm surprised that your requirement isn't something more like "over a given date range, find the count of customers of type 1, type 2 and any type who visited within that range", which would be something like:

ETA，在考虑它之后，我很惊讶你的要求不是“在给定的日期范围内，找到类型1，类型2和在该范围内访问的任何类型的客户的数量”，这将是是这样的：

SELECT TRUNC(visit_date, 'mm') visit_date,
       COUNT(CASE WHEN customer_type = '1' THEN 1 END) c1_count,
       COUNT(CASE WHEN customer_type = '2' THEN 1 END) c2_count,
       COUNT(*) all_possible_customer_count
FROM   customer
where  start_date <= :end_of_date_range
and    end_date >= :start_of_date_range
GROUP BY TRUNC(visit_date, 'mm');

Possibly, with an extra predicate of and visit_date between start_date and end_date (assuming no time elements in the dates).

可能是在start_date和end_date之间有一个额外的谓词和visit_date（假设日期中没有时间元素）。

#1