每月计算新客户 [英] Counting new Customers per Month

问题描述

出于某种原因，我对此感到非常困惑.

I am crazy confused about this for some reason.

基本上，我正在寻找一个查询，该查询将查找自 2010 年以来每月的新客户数量.

Basically, I'm looking for a query that will find the number of new customers per month, since 2010.

我有客户的电子邮件地址(email)、下的所有订单(OrderID)以及下订单的日期(OrderDate).该表是 tblOrder.

I have the customer's email address(email), all orders placed(OrderID), and what date it was placed on(OrderDate). The table is tblOrder.

我知道新客户"是:(a) 从未在日期/月份之前下过订单的人，以及 (b) 在之后至少下过一个订单的人日期/月份

I know that a "new customer" is: (a) someone who's never ordered before the date/month and (b) who has at least one order after the date/month

我希望最终的输出是这样的，使用更简单的方法会更好:

I'd want the output to be something like this in the end, with a simpler method being better:

      01   02   03   04   05   06   07   08   09   10   11   12
2010  ##   ##   ##   ##   ##   ##   ##   ##   ##   ##   ##   ##
2011  ##   ##   ##   ##   ##   ##   ##   ##   ##   ##   ##   ##
2012  ##   ##   ##   ##   ##   ##   ##   ##   ##   ##   ##   ##

<小时>

我得到了这个来工作，但是伙计们，我真的不是程序员，对你们中的一些人来说它可能看起来很简单，但它在我的脑海中并且根本不会和我一起点击.

---

And I was given this to work with, but guys, I'm seriously not a programmer, and it may look simple to some of y'all but it's over my head and not clicking with me at all.

SELECT <customer info> 
FROM <customer table> 
WHERE (SELECT COUNT(<order info>) 
    FROM <order table> 
    WHERE <customer info> = <current customer> 
        AND <date> < <target date>) = 0 
        AND (SELECT COUNT(<order info> 
            FROM <order table> 
            WHERE <customer info> = <current customer> 
            AND <date> > <target date>) > 0

我知道这也不是有效的 SQL.所以我不知道该怎么办.而且我认为它只是拉出一个适用客户的列表(意味着那些在输入的月份之前没有订购的客户)，而不是像我最终想要的那样将它们全部计算出来并合计起来.

I know this isn't valid SQL either. So I don't know what to do with it. And I think it just pulls a list of applicable customers (meaning those who haven't ordered before the inputted month) rather than counting them all up and totaling them like I ultimately want.

推荐答案

尝试:

select yr, [1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12]
from
(select datepart(month,minDate) mth, datepart(year,minDate) yr, count(*) cnt
 from (select min(OrderDate) minDate, max(OrderDate) maxDate
       from tblOrder
       group by email) sq
 where datediff(month, minDate, maxDate) > 0
 group by datepart(month,minDate), datepart(year,minDate)) src
PIVOT
(max(cnt) 
 for mth in ([1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12]) ) pvt

SQLFiddle 此处.

SQLFiddle here.

这篇关于每月计算新客户的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！