计算在特定时间段之前和之后存在的值 [英] Count values which exist before and after a certain time period

问题描述

我有以下简单表格，您也可以在SQL Fiddle 此处找到:

I have the following simple table which you can also find in the SQL Fiddle here:

CREATE TABLE Orders (
    Customer TEXT,
    Order_Date DATE
);

INSERT INTO Orders
(Customer, Order_Date)
VALUES 
("Customer A", "2017-05-23"),
("Customer B", "2018-09-10"),
("Customer B", "2018-12-14"),
("Customer A", "2019-01-03"),
("Customer A", "2019-02-15"),
("Customer C", "2017-09-04"),
("Customer B", "2019-01-09"),
("Customer A", "2019-02-16"),
("Customer C", "2019-02-12"),
("Customer C", "2019-02-15"),
("Customer B", "2018-01-03");

您可以看到该表显示了来自不同客户的订单日期.
我使用下面的SQL来获得

As you can see the table displays the order dates from different customers.
I use the below SQL to get the customers that

a) placed an order in February 2019 and
b) did not place an order in the 12 month before and
c) placed an order before this 12 months period

请参阅此问题的答案这里.

SELECT o.Customer
FROM ORDERS o
GROUP BY o.Customer
HAVING SUM( o.Order_Date >= '2019-02-01' AND o.Order_Date < '2019-03-01' ) > 0 AND
       SUM( o.Order_Date >= '2018-01-02' AND o.Order_Date < '2019-02-01' ) = 0 AND
       SUM( o.Order_Date < '2018-01-02' ) > 0 ;

到目前为止，所有这些都可以正常工作.

All this works fine so far.

但是，现在我要实现的是，SQL既列出满足上述条件的客户，又列出counts客户在February 2019中下的订单数量:

However, now I want to achieve that the SQL is both listing the customers which meet the above criterias but also counts the numbers of orders which the customer placed in February 2019:

在上面的示例中，我期望得到以下结果:

In the example above I would expect the following result:

Customer C     2  

要实现此目的，我需要更改查询中的什么内容?

What do I need to change in my query to achieve this?

推荐答案

只需将其添加到select:

SELECT o.Customer,
       SUM( o.Order_Date >= '2019-02-01' AND o.Order_Date < '2019-03-01' ) as num_feb_orders
FROM ORDERS o
GROUP BY o.Customer
HAVING SUM( o.Order_Date >= '2019-02-01' AND o.Order_Date < '2019-03-01' ) > 0 AND
       SUM( o.Order_Date >= '2018-01-02' AND o.Order_Date < '2019-02-01' ) = 0 AND
       SUM( o.Order_Date < '2018-01-02' ) > 0 ;

这篇关于计算在特定时间段之前和之后存在的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！