mysql - sql查询从另一个表的输入中提取数据

Question

Customer table

customer_id|household_id|loyalty_member|gender|city|state|zip|phone_opt_in|email_opt_in|mail_opt_in
-----------------------------------------------------------------------------

Coupon Table    
offer_code_used|offer_desc|channel|camp_code|coup_start|coup_end|total
------------------------------------------------------------------

transaction table

customer_id|trans_id|offer_code_used|trans_date|trans_type|revenue
--------------------------------------------------------------

你好，

我有两个具有公共(public)元素(offer_code_used)的表。我需要做的第一件事是查找 12 月份兑换了多少优惠券，我使用以下代码执行此操作(日期格式为带有 yyyymmdd 的 varchar):

select count(offer_code_used)
from coupon
where coupon_start_date >=20161201 and
      coupon_end_date <=20161231

然后我想知道有多少独立购物者以及总销售收入。对于独特的购物者，我使用的是:

select count(distinct customer_id)
from customer

收入:

select sum(revenue)
from customer

我最后想知道 2016 年全年每月兑换永不过期优惠券的数量(假设代码是“STACK”)，以及其中有多少是由忠诚成员(member)兑换的:

P.Salmon 建议这个查询(我稍微修改了它以便在 postgresql 中使用)，但我在内连接行出现语法错误，我似乎无法纠正。我不相信这个解决方案是正确的，但是我特别需要的是两列；一列包含给定月份内兑换优惠券的数量，另一列包含该月内使用优惠券的忠诚成员(member)的数量。但这绝对为解决这个问题指明了正确的方向

select T.offer_code_used,
         concat(substring(T.trans_date,1,4),substring(T.trans_date,5,2)) yyyymm,
         SUM(CASE WHEN C.LOYALTY_MEMBER = 'n' then 1 else 0 end) 'notloyal',
         SUM(CASE WHEN C.LOYALTY_MEMBER = 'y' then 1 else 0 end) 'loyal',
         count(*) as Total
from transaction T
where T.offer_code_used = 'STACK'
INNER JOIN customer C
ON (T.customer_id = C.customer_id)
group by T.offer_code_used, concat(substring(t.trans_date,1,4),substring(t.trans_date,5,2)) yyyymm
with rollup

这就是我遇到了一点麻烦的地方，这不应该起作用，而且我不知道如何检查其中有多少是由忠诚成员(member)兑换的。我知道 customer_id 涉及联接，但我似乎想不出一种方法来实现它。

感谢任何帮助

Answer 1

所提出的解决方案的唯一问题是您的 JOIN 的位置，从概念上讲，它是正确的。下一个查询纠正了这个错误:

SELECT
    t.offer_code_used,
    substring(t.trans_date,1,6) AS yyyymm,
    SUM(CASE WHEN C.LOYALTY_MEMBER = 'n' THEN 1 ELSE 0 END) AS notloyal,
    SUM(CASE WHEN C.LOYALTY_MEMBER = 'y' THEN 1 ELSE 0 END) AS loyal,
    count(*) as total
FROM
    trans t    /* I've named the table `trans` to avoid using a reserved word */
    JOIN customer c ON (c.customer_id = t.customer_id)
WHERE
    t.offer_code_used = 'STACK'
GROUP BY 
    t.offer_code_used,
    substring(t.trans_date,1,6)

您可以在 SQLFiddle 上查看。您得到的答案是:

| offer_code_used | yyyymm | notloyal | loyal | total |
|-----------------|--------|----------|-------|-------|
|           STACK | 201612 |        0 |     1 |     1 |
|           STACK | 201701 |        1 |     1 |     2 |  

<小时/>

完整定义:

CREATE TABLE customer
(
  customer_id INTEGER NOT NULL PRIMARY KEY,
  household_id INTEGER,
  loyalty_member CHARACTER(1) NOT NULL DEFAULT 'n' /* ('n'|'y') representing a boolean */,
  gender character(1)  /* M | F */,
  city character varying(100),
  state character(2),
  zip character(10),
  phone_opt_in INTEGER DEFAULT 0,
  email_opt_in INTEGER DEFAULT 0,
  mail_opt_in  INTEGER DEFAULT 0,
  CHECK (loyalty_member in ('y', 'n'))
) ;

INSERT INTO 
  customer
  (customer_id, household_id, loyalty_member)
VALUES 
  (1, 1, 'y'),
  (2, 1, 'n'),
  (3, 1, 'y') ;

CREATE TABLE coupon 
(
  offer_code_user INTEGER NOT NULL,    
  offer_code_used CHARACTER(10) NOT NULL PRIMARY KEY,
  offer_desc CHARACTER VARYING(100),
  channel CHARACTER VARYING(100),
  camp_code CHARACTER VARYING(100),
  coup_start DATE,
  coup_end DATE,
  total INTEGER
) ;

INSERT INTO 
  coupon 
  (offer_code_user, offer_code_used, offer_desc)
VALUES
  (1, 'STACK', 'Stack offer'),
  (1, 'PROMO1', 'Promo offer') ;

CREATE TABLE trans
(
  customer_id INTEGER NOT NULL REFERENCES customer(customer_id),
  trans_id INTEGER NOT NULL PRIMARY KEY,
  offer_code_used CHARACTER(10) NOT NULL REFERENCES coupon(offer_code_used),
  trans_date CHARACTER(10) /* 'yyyymmdd' should actually be a DATE */,
  trans_type CHARACTER VARYING(100),
  revenue NUMERIC
) ;

INSERT INTO 
  trans 
  (customer_id, trans_id, offer_code_used, trans_date)
VALUES 
  (1, 1, 'STACK', '20170101'),
  (2, 2, 'STACK', '20170101'),
  (3, 3, 'STACK', '20161230') ;