데이터를 받고 파악하는 법, 시간 데이터 파악

select count(*) from prod_order po; #3431

select count(DISTINCT order_no) from prod_order po; #3215 차이가 나넵 업자인가 한방에 많이 사네

SELECT min(order_dt) as dt_start, max(order_dt) as dt_end from prod_order po ; #날자 데이터 언제부터 언제까지인지 봐봐야지.

SELECT DISTINCT product_name from prod_order po ; #A만 있구먼

SELECT DISTINCT `options` from prod_order po ;

SELECT min(qty), round(avg(qty),3), max(qty) from prod_order po ; #양을 볼때 최소값과 평균, 최대값도 한 번 봐보자.

select * from prod_order po where qty >= 2;

SELECT count(*) from prod_order po WHERE qty >= 2; # 32

SELECT count(DISTINCT order_no) from prod_order po WHERE qty >= 2; # 동일한 옵션 2개 이상 산 주문건이 몇 건인지 30

SELECT DISTINCT `options`, count(*), qty from prod_order po WHERE qty >=2 group by `options` order by count(*) desc ;

 

 

SELECT order_dt from prod_order po limit 5;

SELECT YEAR(order_dt) from prod_order po limit 5;

SELECT YEAR(order_dt) as `year` from prod_order po limit 5;

SELECT YEAR(order_dt), count(*) from prod_order po group by YEAR(order_dt);

SELECT YEAR(order_dt),month(order_dt), count(*), sum(qty) from prod_order po group by YEAR(order_dt), month(order_dt ) ;

SELECT DATE_FORMAT(order_dt, '''%Y-%m') as yyyymm from prod_order po limit 5;

SELECT DATE_FORMAT(order_dt, '''%Y-%m') as yyyymm, count(*) from prod_order po group by yyyymm;

SELECT order_dt ,DAYOFWEEK(order_dt) from prod_order po limit 5;

#주중 (2,3,4,5,6)

SELECT count(*) from prod_order po where DAYOFWEEK(order_dt)BETWEEN 2 and 6;

# 주말 (7,1)

SELECT count(*) from prod_order po where DAYOFWEEK(order_dt) in (7,1);

SELECT count(*) from prod_order po ;

SELECT count(DISTINCT dayofweek(order_dt)) from prod_order po ;

# 시간대별 주문 건수 집계

SELECT hour(order_dt) as H, count(*) from prod_order po group by H order by count(*) desc ;

 

select LENGTH(order_dt) from prod_order po limit 5;

select DISTINCT LENGTH(order_no) from prod_order po ;

 

SELECT DISTINCT left(`options`, 3) from prod_order po;

SELECT DISTINCT left(`options`, 3) as a, count(*) from prod_order po group by a;

SELECT DISTINCT left(`options`, 3) as a, count(*) from prod_order po where year(order_dt)=2023 group by a;

 

SELECT DISTINCT r(`options`, 3) as a, count(*) from prod_order po where year(order_dt)=2023 group by a;

SELECT DISTINCT trim(right(`options`, 2)) as a, count(*) from prod_order po group by a;