检查表中较早的特定ID行是否出现

Question

I have a table of customer orders which is something like below:

order_id | customer_id |    date    | ...
-----------------------------------------
   583         192       2015-05-01   ...
   734         143       2015-06-04   ...
   801         455       2015-07-02   ...
   ...         ...           ...      ...

I want to find how many new customers I receive in a given month. A new customer can be found by querying the orders table above and finding the number of customer_id values that appear in that month but do not appear before that month (after that month is fine). I'm looking for the first instance of a new customer_id.

Is there a way to do this in one SQL statement? I could do something like the below I think:

• get all order_id and DISTINCT customer_id from a month by querying database using LIKE YYYY-MM-%
• check the table for instances of customer_id with a smaller order_id (it stands to reason a smaller order_id will be an earlier order)
• if found, skip
• if not found, increment a counter

Answer 1

Assuming date_From is your period start date and date_To is period end date you can do it something like:

select distinct T1.customer_id
from your_table as T1
    left outer join your_Table as T2 on 
       T1.customer_id = T2.customer_id and T2.date < date_From
where
    T1.date <= date_To
    and T1.date >= date_From
    and T2.customer_id is null

Here joining to the same table using same customer_id and date prior to your period and checking that join has no result (by T2.customer_id is null) ensures your customer_id first appears in the order of your period and not earlier.