#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='test@XXX.co.jp', 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='test@XXX.co.jp', 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='test@XXX.co.jp';
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='test@XXX.co.jp', 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
```