新老用户

-- drop table vn09jj5.temp_order ; 
create table if not exists vn09jj5.temp_order
(
 store_id int,
 phone varchar(11),
 order_id varchar(20),
 paid_date date,
 register_date date
)
stored as textfile ; 


insert into vn09jj5.temp_order(store_id,phone,order_id,paid_date,register_date)
values
(1094,'1314***1000','1000011','2020-06-01','2020-06-01'),
(1094,'1314***1000','1000012','2020-06-01','2020-06-01'),
(1094,'1314***1000','1000013','2020-06-01','2020-06-01'),
(1094,'1314***1000','1000021','2020-06-02','2020-06-01'),
(1094,'1314***1000','1000022','2020-06-02','2020-06-01'),
(1094,'1314***1000','1000031','2020-06-03','2020-06-01');

insert into vn09jj5.temp_order(store_id,phone,order_id,paid_date,register_date)
values
(3409,'1314***5555','2000061','2020-06-01',null),
(3409,'1314***5555','2000062','2020-06-01',null),
(3409,'1314***5555','2000063','2020-06-01',null),
(3409,'1314***5555','2000071','2020-06-02',null),
(3409,'1314***5555','2000072','2020-06-02',null),
(3409,'1314***5555','2000081','2020-06-03',null);

select * from vn09jj5.temp_order ;

+-----------+--------------+-----------+-------------+----------------+
| store_id  |    phone     | order_id  |  paid_date  | register_date  |
+-----------+--------------+-----------+-------------+----------------+
| 1094      | 1314***1000  | 1000011   | 2020-06-01  | 2020-06-01     |
| 1094      | 1314***1000  | 1000012   | 2020-06-01  | 2020-06-01     |
| 1094      | 1314***1000  | 1000013   | 2020-06-01  | 2020-06-01     |
| 1094      | 1314***1000  | 1000021   | 2020-06-02  | 2020-06-01     |
| 1094      | 1314***1000  | 1000022   | 2020-06-02  | 2020-06-01     |
| 1094      | 1314***1000  | 1000031   | 2020-06-03  | 2020-06-01     |
| 3409      | 1314***5555  | 2000061   | 2020-06-01  | NULL           |
| 3409      | 1314***5555  | 2000062   | 2020-06-01  | NULL           |
| 3409      | 1314***5555  | 2000063   | 2020-06-01  | NULL           |
| 3409      | 1314***5555  | 2000071   | 2020-06-02  | NULL           |
| 3409      | 1314***5555  | 2000072   | 2020-06-02  | NULL           |
| 3409      | 1314***5555  | 2000081   | 2020-06-03  | NULL           |
+-----------+--------------+-----------+-------------+----------------+

 

当天的所有单都算新用户(NEW)

select 
 *,
 case when (paid_date=register_date) then 'NEW' else 'old' end as c_type
from vn09jj5.temp_order
order by phone,paid_date,order_id ;

+-----------+--------------+-----------+-------------+----------------+---------+
| store_id  |    phone     | order_id  |  paid_date  | register_date  | c_type  |
+-----------+--------------+-----------+-------------+----------------+---------+
| 1094      | 1314***1000  | 1000011   | 2020-06-01  | 2020-06-01     | NEW     |
| 1094      | 1314***1000  | 1000012   | 2020-06-01  | 2020-06-01     | NEW     |
| 1094      | 1314***1000  | 1000013   | 2020-06-01  | 2020-06-01     | NEW     |
| 1094      | 1314***1000  | 1000021   | 2020-06-02  | 2020-06-01     | old     |
| 1094      | 1314***1000  | 1000022   | 2020-06-02  | 2020-06-01     | old     |
| 1094      | 1314***1000  | 1000031   | 2020-06-03  | 2020-06-01     | old     |
| 3409      | 1314***5555  | 2000061   | 2020-06-01  | NULL           | old     |
| 3409      | 1314***5555  | 2000062   | 2020-06-01  | NULL           | old     |
| 3409      | 1314***5555  | 2000063   | 2020-06-01  | NULL           | old     |
| 3409      | 1314***5555  | 2000071   | 2020-06-02  | NULL           | old     |
| 3409      | 1314***5555  | 2000072   | 2020-06-02  | NULL           | old     |
| 3409      | 1314***5555  | 2000081   | 2020-06-03  | NULL           | old     |
+-----------+--------------+-----------+-------------+----------------+---------+

 

当天的第一单算新用户(NEW)

select 
 *,
 ROW_NUMBER() over(partition by store_id,phone,paid_date order by order_id) as par_order
from vn09jj5.temp_order
order by phone,paid_date,order_id ;

+-----------+--------------+-----------+-------------+----------------+------------+
| store_id  |    phone     | order_id  |  paid_date  | register_date  | par_order  |
+-----------+--------------+-----------+-------------+----------------+------------+
| 1094      | 1314***1000  | 1000011   | 2020-06-01  | 2020-06-01     | 1          |
| 1094      | 1314***1000  | 1000012   | 2020-06-01  | 2020-06-01     | 2          |
| 1094      | 1314***1000  | 1000013   | 2020-06-01  | 2020-06-01     | 3          |
| 1094      | 1314***1000  | 1000021   | 2020-06-02  | 2020-06-01     | 1          |
| 1094      | 1314***1000  | 1000022   | 2020-06-02  | 2020-06-01     | 2          |
| 1094      | 1314***1000  | 1000031   | 2020-06-03  | 2020-06-01     | 1          |
| 3409      | 1314***5555  | 2000061   | 2020-06-01  | NULL           | 1          |
| 3409      | 1314***5555  | 2000062   | 2020-06-01  | NULL           | 2          |
| 3409      | 1314***5555  | 2000063   | 2020-06-01  | NULL           | 3          |
| 3409      | 1314***5555  | 2000071   | 2020-06-02  | NULL           | 1          |
| 3409      | 1314***5555  | 2000072   | 2020-06-02  | NULL           | 2          |
| 3409      | 1314***5555  | 2000081   | 2020-06-03  | NULL           | 1          |
+-----------+--------------+-----------+-------------+----------------+------------+

 

select 
 *,
 par_order,
 case when (paid_date=register_date and par_order=1) then 'NEW' else 'old' end as c_type
from (
--1
select 
 *, ROW_NUMBER() over(partition by store_id, phone,paid_date order by order_id) as par_order
from vn09jj5.temp_order
--end1
) as a
order by phone,paid_date,order_id ;

+-----------+--------------+-----------+-------------+----------------+------------+---------+
| store_id  |    phone     | order_id  |  paid_date  | register_date  | par_order  | c_type  |
+-----------+--------------+-----------+-------------+----------------+------------+---------+
| 1094      | 1314***1000  | 1000011   | 2020-06-01  | 2020-06-01     | 1          | NEW     |
| 1094      | 1314***1000  | 1000012   | 2020-06-01  | 2020-06-01     | 2          | old     |
| 1094      | 1314***1000  | 1000013   | 2020-06-01  | 2020-06-01     | 3          | old     |
| 1094      | 1314***1000  | 1000021   | 2020-06-02  | 2020-06-01     | 1          | old     |
| 1094      | 1314***1000  | 1000022   | 2020-06-02  | 2020-06-01     | 2          | old     |
| 1094      | 1314***1000  | 1000031   | 2020-06-03  | 2020-06-01     | 1          | old     |
| 3409      | 1314***5555  | 2000061   | 2020-06-01  | NULL           | 1          | old     |
| 3409      | 1314***5555  | 2000062   | 2020-06-01  | NULL           | 2          | old     |
| 3409      | 1314***5555  | 2000063   | 2020-06-01  | NULL           | 3          | old     |
| 3409      | 1314***5555  | 2000071   | 2020-06-02  | NULL           | 1          | old     |
| 3409      | 1314***5555  | 2000072   | 2020-06-02  | NULL           | 2          | old     |
| 3409      | 1314***5555  | 2000081   | 2020-06-03  | NULL           | 1          | old     |
+-----------+--------------+-----------+-------------+----------------+------------+---------+