You can not select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
87 lines
2.4 KiB
87 lines
2.4 KiB
-- 微信用户数-日
|
|
SELECT
|
|
COUNT(1) 日增加微信用户数
|
|
FROM lpk_customer lc
|
|
WHERE lc.createTime>='2024-01-30' AND lc.createTime<'2024-01-31'
|
|
|
|
-- 微信用户数-累计
|
|
SELECT
|
|
COUNT(1) 微信用户总数
|
|
FROM lpk_customer lc
|
|
|
|
-- 购买人数-日 指定日期线上下单加上绑定个人卡和企业卡的用户数(同一用户记一条)
|
|
SELECT COUNT(1) FROM (
|
|
SELECT
|
|
oo.openId openid
|
|
FROM ord_order oo
|
|
WHERE oo.payStatus=4 AND oo.payTime>='2024-01-30' AND oo.payTime<'2024-01-31'
|
|
UNION
|
|
SELECT
|
|
lc.wxMpOpenid openid
|
|
FROM emp_card ec
|
|
LEFT JOIN lpk_customer lc ON lc.sid = ec.customerSid
|
|
WHERE ec.customerSid IS NOT NULL AND ec.customerSid <> ''
|
|
AND ec.bindDate>='2024-01-30' AND ec.bindDate<'2024-01-31'
|
|
UNION
|
|
SELECT
|
|
lc.wxMpOpenid openid
|
|
FROM lpk_giftcard lg
|
|
LEFT JOIN lpk_customer lc ON lc.sid = lg.customerSid
|
|
WHERE lg.customerSid IS NOT NULL AND lg.customerSid <> ''
|
|
AND lg.bindDate>='2024-01-30' AND lg.bindDate<'2024-01-31'
|
|
) t1
|
|
|
|
-- 购买人数-累计 线上下单加上绑定个人卡和企业卡的用户数(同一用户记一条)
|
|
SELECT COUNT(1) FROM (
|
|
SELECT
|
|
oo.openId openid
|
|
FROM ord_order oo
|
|
UNION
|
|
SELECT
|
|
lc.wxMpOpenid openid
|
|
FROM emp_card ec
|
|
LEFT JOIN lpk_customer lc ON lc.sid = ec.customerSid
|
|
WHERE ec.customerSid IS NOT NULL AND ec.customerSid <> ''
|
|
UNION
|
|
SELECT
|
|
lc.wxMpOpenid openid
|
|
FROM lpk_giftcard lg
|
|
LEFT JOIN lpk_customer lc ON lc.sid = lg.customerSid
|
|
WHERE lg.customerSid IS NOT NULL AND lg.customerSid <> ''
|
|
) t1
|
|
|
|
-- 复购用户数-累计
|
|
SELECT COUNT(1) FROM (
|
|
SELECT openid,COUNT(1) num FROM (
|
|
SELECT
|
|
oo.openId openid
|
|
FROM ord_order oo
|
|
UNION ALL
|
|
SELECT
|
|
lc.wxMpOpenid openid
|
|
FROM emp_card ec
|
|
LEFT JOIN lpk_customer lc ON lc.sid = ec.customerSid
|
|
WHERE ec.customerSid IS NOT NULL AND ec.customerSid <> ''
|
|
UNION ALL
|
|
SELECT
|
|
lc.wxMpOpenid openid
|
|
FROM lpk_giftcard lg
|
|
LEFT JOIN lpk_customer lc ON lc.sid = lg.customerSid
|
|
WHERE lg.customerSid IS NOT NULL AND lg.customerSid <> ''
|
|
) t GROUP BY openid
|
|
) tt WHERE tt.num>1
|
|
|
|
-- 两次以上提货用户数-累计
|
|
SELECT COUNT(1) FROM (
|
|
SELECT openid,COUNT(1) num FROM (
|
|
SELECT
|
|
lc.wxMpOpenid openid
|
|
FROM lpk_reserve_order lro
|
|
LEFT JOIN lpk_customer lc ON lc.sid = lro.customerSid
|
|
UNION ALL
|
|
SELECT
|
|
lc.wxMpOpenid openid
|
|
FROM vege_cellar_reserve_order vcro
|
|
LEFT JOIN lpk_customer lc ON lc.sid = vcro.customerSid
|
|
) t GROUP BY t.openid
|
|
) tt WHERE tt.num>1
|
|
|