EC-Cube2で普段使っているSQLをできる限り公開

#EC-Cube2で使っているSQL

会員登録数をランキングするためのSQL

　これにより、いつの登録が多かったのかがわかります。
　これを元に、なにのキャンペーンを行なったのかを考えています。

    select count(customer_id), DATE_FORMAT(create_date, '%Y-%m-%d') 
    from dtb_customer 
    group by  DATE_FORMAT(create_date, '%Y-%m-%d')

過去１ヶ月間の全商品ランキング6件を取得する

　売り上げ数上位と、そこから出されている売り上げをだすことができます。
　日々確認していると、売り上げ数と売り上げは比例しないことがわかります。

      SELECT
      od.product_id, od.product_name, p.main_image,
      count(*) as cnt, sum(od.price) as price
      FROM
      ( SELECT order_id FROM dtb_order WHERE
      create_date > DATE_ADD(now(), INTERVAL -1 month) ) o
      INNER JOIN dtb_order_detail od ON(o.order_id = od.order_id)
      INNER JOIN dtb_order as do ON (do.order_id = od.order_id)
      INNER JOIN dtb_products p ON(od.product_id = p.product_id)
      INNER JOIN dtb_product_categories pc ON (p.product_id = pc.product_id)

      where pc.category_id>8
      and do.create_date> DATE_ADD(now(), INTERVAL -1 month)

      GROUP BY od.product_id
      order by count(od.product_id) desc
      limit 6

前日の会員登録数を取得する

　前日の会員登録数は、当日の会員登録数との比較に使っています。

     select count(customer_id) as count from dtb_customer where create_date BETWEEN (CURDATE() - INTERVAL 0 DAY) and (CURDATE() - INTERVAL -1 DAY)

当日の会員登録数を取得する

　毎日の会員登録数を確認していると、
　曜日での傾向、先週や先月との違いがわかってきます。

      select count(customer_id) as count from dtb_customer where create_date BETWEEN (CURDATE() - INTERVAL 1 DAY) and (CURDATE() - INTERVAL 0 DAY)

```






## 男性会員のうち、未購入の人のメールアドレスを取得する
性別ごとに取得していて、これにより、男女への訴求方法を考えています。




```sql

 select count(dc.customer_id)
 from dtb_customer as dc
 where 
 dc.last_buy_date is null
 and dc.sex=1
 and create_date > DATE_ADD(now(), INTERVAL -1 month)

```


## 個人情報をマスクする
テストサーバを作るときに使っています。

```sql
update dtb_shipping set shipping_addr01='都庁前', shipping_addr02='１−２−３';
update dtb_csv_sql set csv_sql='';
update dtb_customer set email='[e-mail]', addr01='都庁前' ,addr02='1-2-3';
update dtb_customer set tel01='090', tel02='1234' ,tel03='5678';
update dtb_customer set salt='salt', reminder_answer='sfddsfwoiusjdf';
update dtb_customer set name01='なまえ１', name02='なまえ2';
update dtb_customer set kana01='ナマエ1', kana02='ナマエ2';
update dtb_customer set zip01='012', zip02='0123';
update dtb_mail_history set mail_body='本文本文本文本文';
update dtb_order set order_name01='注文者なまえ1', order_name02='注文者なまえ２';
update dtb_order set order_kana01='チュウモンシャ1', order_kana02='チュウモンシャ2';
update dtb_order set order_email='[e-mail]', order_tel01='090', order_tel02='1234', order_tel03='5678';
update dtb_order set order_zip01='000', order_zip02='1234';
update dtb_order set order_addr01='都庁前', order_addr02='１−２−３';
update dtb_order_temp set memo01='' and memo02='' and memo03='';
update dtb_order_temp set memo04='' and memo05='' and memo06='';
update dtb_order_temp set memo07='' and memo08='' and memo09='';
update dtb_order_temp set order_name01='注文者なまえ1', order_name02='注文者なまえ2';
update dtb_order_temp set order_kana01='チュウモンシャ1', order_kana02='チュウモンシャ2';
update dtb_order_temp set order_email='[e-mail]';
update dtb_order_temp set order_tel01='090', order_tel02='1234', order_tel03='5678';
update dtb_order_temp set order_zip01='000', order_zip02='1234';
update dtb_order_temp set order_addr01='都庁前', order_addr02='１−２−３';
update dtb_other_deliv set name01='なまえ', name02='なまえ';
update dtb_other_deliv set kana01='ナマエ1', kana02='ナマエ2';
update dtb_other_deliv set zip01='000', zip02='ナマエ';
update dtb_other_deliv set addr01='都庁前', addr02='１−２−３';
update dtb_other_deliv set tel01='090', tel02='1234', tel03='5678';
update dtb_send_customer set email='[e-mail]', name='なまえ';
update dtb_session set sess_data='';
update dtb_shipping set shipping_name01='名前１', shipping_name02='名前２';
update dtb_shipping set shipping_kana01='ナマエ1', shipping_kana02='ナマエ2';
update dtb_shipping set shipping_tel01='090', shipping_tel02='1234', shipping_tel03='5678';
update dtb_shipping set shipping_zip01='000', shipping_zip02='1234';
update dtb_shipping set shipping_addr01='都庁前', shipping_addr02='１−２−３';
```

## 首都圏人口の多い場所だけのユーザを1年分取得する
シェア確認のために使っています。

```sql
select * from dtb_customer
where create_date > DATE_ADD(now(), INTERVAL -1 year)
and pref in(11,12,13,14,23,27)
and del_flg=0
```


## 特定商品を購入した人全員を会員登録時間ととも男女別でに出力
これにより出力されるリストを男女＋時間セグメントを作成してあるメルマガシステムに繋ぎ、メールマガジンを配信しています。　これにより、複数のリストを管理しなくてよくなり、時間ごとの開封状況も確認でき、unsubsclive処理もまとめられます。少し手間になりますが、便利な方法です。

```sql

select order_email, substr(dtb_order.create_date, -8, 2) 
from dtb_order ,`dtb_order_detail`, dtb_customer　where 
dtb_order.order_id = dtb_order_detail.order_id
and　dtb_order.customer_id = dtb_customer.customer_id
and　product_id in (100,200,300,400,500,600,700,800)
and　dtb_customer.del_flg=0
and　sex = 1
group by order_email

```


## 当日の売上数を取得する
DATEにキャストした値同士を比べることで、簡単にしています。

```sql

SELECT * from dtb_order where del_flg=0 and  DATE(SUBSTR(create_date,1,10)) = DATE(NOW()) -1

```


## 過去1ヶ月のクーポン利用
```
select * from dtb_couponused, db_coupon
where dtb_couponused.coupon_id=dtb_coupon.coupon_id
and create_date>adddate(now(),  -1 month
```