客快物流大数据项目（八十）：用户标签开发

举报
Lansonli 发表于 2022/12/19 16:21:27 2022/12/19
【摘要】 ​用户标签开发一、​​​​​​​​​​​​​​用户首单时间SELECT t1.cid, t1.cdt, t2.nameFROM (SELECT tcsi.ciid AS cid, MIN(tcsi.cdt) AS cdt FROM tbl_consumer_sender_info tcsi GROUP BY tcsi.ciid ORDER BY cdt ASC...

​用户标签开发

一、​​​​​​​​​​​​​​用户首单时间

SELECT
  t1.cid,
  t1.cdt,
  t2.name
FROM
  (SELECT
    tcsi.ciid AS cid,
    MIN(tcsi.cdt) AS cdt
  FROM
    tbl_consumer_sender_info tcsi
  GROUP BY tcsi.ciid
  ORDER BY cdt ASC) t1
  LEFT JOIN tbl_customer t2
    ON t2.id = t1.cid
WHERE NAME IS NOT NULL;


二、​​​​​​​​​​​​​​用户首单地址

----用户首单收货地址
select
t1.ciid,
t2.name,
t1.cdt,
t3.detailaddr
from
(
select                                   ---每个用户的首次下单时间
tcsi.ciid,
min(tcsi.cdt) as cdt
from tbl_consumer_sender_info tcsi
group by tcsi.ciid
) t1
left join tbl_customer t2
on t1.ciid=t2.id
left join (
select                                   --每个用户的首次收货地址
consumerid,
min(tcam.cdt) as cdt,
ta.detailaddr
from 
tbl_consumer_address_map tcam
left join tbl_address ta
on tcam.addressid=ta.id
group by consumerid,ta.detailaddr 
) t3
on t3.consumerid=t2.id


三、用户首单来源

SELECT
  t1.cid,
  t1.cdt,
  t2.name,
  t3.codedesc AS reg_channel_name
FROM
  (SELECT
    tcsi.ciid AS cid,
    MIN(tcsi.cdt) AS cdt
  FROM
    tbl_consumer_sender_info tcsi
  GROUP BY tcsi.ciid
  ORDER BY cdt ASC) t1
  LEFT JOIN tbl_customer t2
    ON t2.id = t1.cid
  LEFT JOIN tbl_codes t3
    ON t2.regchannelid = t3.code and t3.type=18;


四、用户首单省份

SELECT
  tc.id,
  tc.name,
  tas2.id AS province_id,
  tas2.name,
  tcs.cdt
FROM
  (SELECT
    ciid,
    MIN(cdt) AS cdt
  FROM
    tbl_consumer_sender_info
  GROUP BY ciid) tcs
  LEFT JOIN tbl_customer tc
    ON tcs.ciid = tc.id
  LEFT JOIN
    (SELECT
      tcm1.consumerid,
      MIN(tcm1.cdt) AS cdt,
      MIN(tcm1.addressid) AS address_id
    FROM
      tbl_consumer_address_map tcm1
    GROUP BY tcm1.consumerid) tcm
    ON tcm.consumerid = tc.id
  LEFT JOIN tbl_address ta
    ON ta.id = tcm.address_id
  LEFT JOIN tbl_areas tas
    ON tas.id = ta.areaid
  LEFT JOIN tbl_areas tas1
    ON tas.pid = tas1.id
  LEFT JOIN tbl_areas tas2
    ON tas1.pid = tas2.id
WHERE tc.name IS NOT NULL;


五、用户首单城市

SELECT
  tc.id,
  tc.name,
  tas1.id,
  tas1.name AS city_name,
  tcs.cdt
FROM
  (SELECT
    ciid,
    MIN(CAST(cdt AS INT)) AS cdt
  FROM
    tbl_consumer_sender_info
  GROUP BY ciid) tcs
  LEFT JOIN tbl_customer tc
    ON tcs.ciid = tc.id
  LEFT JOIN tbl_consumer_address_map tcm
    ON tcm.consumerid = tc.id
  LEFT JOIN tbl_address ta
    ON ta.id = tcm.addressid
  LEFT JOIN tbl_areas tas
    ON tas.id = ta.areaid
  LEFT JOIN tbl_areas tas1
    ON tas.pid = CAST(tas1.id AS INT);


六、用户首单地区

SELECT
  tc.id,
  tc.name,
  tas.id,
  tas.name AS area_name,
  tcs.cdt
FROM
  (SELECT
    ciid,
    MIN(CAST(cdt AS INT)) AS cdt
  FROM
    tbl_consumer_sender_info
  GROUP BY ciid) tcs
  LEFT JOIN tbl_customer tc
    ON tcs.ciid = tc.id
  LEFT JOIN tbl_consumer_address_map tcm
    ON tcm.consumerid = tc.id
  LEFT JOIN tbl_address ta
    ON ta.id = tcm.addressid
  LEFT JOIN tbl_areas tas
    ON tas.id = ta.areaid;


七、​​​​​​​​​​​​​​最后一次收货省份

SELECT
  tc.id,
  tc.name,
  tas2.id AS province_id,
  tas2.name,
  tcs.cdt
FROM
  (SELECT
    ciid,
    MAX(cdt) AS cdt
  FROM
    tbl_consumer_sender_info
  GROUP BY ciid) tcs
  LEFT JOIN tbl_customer tc
    ON tcs.ciid = tc.id
  LEFT JOIN
    (SELECT
      tcm1.consumerid,
      MIN(tcm1.cdt) AS cdt,
      MIN(tcm1.consumerid) AS address_id
    FROM
      tbl_consumer_address_map tcm1
    GROUP BY tcm1.consumerid) tcm
    ON tcm.consumerid = tc.id
  LEFT JOIN tbl_address ta
    ON ta.id = tcm.address_id
  LEFT JOIN tbl_areas tas
    ON tas.id = ta.areaid
  LEFT JOIN tbl_areas tas1
    ON tas.pid = CAST(tas1.id AS INT)
  LEFT JOIN tbl_areas tas2
    ON tas1.pid = CAST(tas2.id AS INT)
WHERE tc.name IS NOT NULL;


八、​​​​​​​​​​​​​​最后一次收货地区

SELECT
  tc.id,
  tc.name,
  tas1.id,
  tas1.name AS city_name,
  tcs.cdt
FROM
  (SELECT
    ciid,
    MAX(CAST(cdt AS INT)) AS cdt
  FROM
    tbl_consumer_sender_info
  GROUP BY ciid) tcs
  LEFT JOIN tbl_customer tc
    ON tcs.ciid = tc.id
  LEFT JOIN tbl_consumer_address_map tcm
    ON tcm.consumerid = tc.id
  LEFT JOIN tbl_address ta
    ON ta.id = tcm.addressid
  LEFT JOIN tbl_areas tas
    ON tas.id = ta.areaid
  LEFT JOIN tbl_areas tas1
    ON tas.pid = CAST(tas1.id AS INT);


九、常用收货省份

SELECT
  t2.cid,
  t2.recvaddressid,
  t2.cnt,
  tas3.id AS province_id,
  tas3.name AS province_name
FROM
  (SELECT
    t1.cid,
    t1.recvaddressid,
    t1.cnt
  FROM
    (SELECT
      tep.cid,
      tep.recvaddressid,
      COUNT(tep.recvaddressid) AS cnt,
      MAX(tep.cdt) AS cdt
    FROM
      tbl_express_package tep
    GROUP BY tep.cid,
      tep.recvaddressid) t1
  WHERE t1.cid = 73
  ORDER BY t1.cnt,
    t1.cdt DESC
  LIMIT 1) t2
  LEFT JOIN tbl_address ta
    ON (ta.id = t2.recvaddressid)
  LEFT JOIN tbl_areas tas1
    ON (tas1.id = ta.areaid)
  LEFT JOIN tbl_areas tas2
    ON (tas1.pid = tas1.id)
  LEFT JOIN tbl_areas tas3
    ON (tas2.pid = tas3.id);


十、常用收货地区

SELECT
  t2.cid,
  t2.recvaddressid,
  t2.cnt,
  tas2.id AS city_id,
  tas2.name AS city_name
FROM
  (SELECT
    t1.cid,
    t1.recvaddressid,
    t1.cnt
  FROM
    (SELECT
      tep.cid,
      tep.recvaddressid,
      COUNT(tep.recvaddressid) AS cnt,
      MAX(tep.cdt) AS cdt
    FROM
      tbl_express_package tep
    GROUP BY tep.cid,
      tep.recvaddressid) t1
  WHERE t1.cid = 73
  ORDER BY t1.cnt,
    t1.cdt DESC
  LIMIT 1) t2
  LEFT JOIN tbl_address ta
    ON (ta.id = t2.recvaddressid)
  LEFT JOIN tbl_areas tas1
    ON (tas1.id = ta.areaid)
  LEFT JOIN tbl_areas tas2
    ON (tas1.pid = tas1.id);


十一、最后一次使用户号码

SELECT
  tep.cid,
  tc.name,
  ta.id AS address_id,
  ta.detailaddr,
  ta.tel,
  tep.cdt
FROM
  (SELECT
    cid,
    sendaddressid,
    cdt
  FROM
    tbl_express_package
  WHERE cid = 30
  ORDER BY cdt DESC
  LIMIT 1) tep
  LEFT JOIN tbl_customer tc
    ON (tc.id = tep.cid)
  LEFT JOIN tbl_address ta
    ON (ta.id = tep.sendaddressid);


十二、常用手机号码

SELECT
  t1.cid,
  t1.recvaddressid,
  t1.cnt,
  tc.name,
  tc.tel
FROM
  (SELECT
    t1.cid,
    t1.recvaddressid,
    t1.cnt
  FROM
    (SELECT
      tep.cid,
      tep.recvaddressid,
      COUNT(tep.recvaddressid) AS cnt,
      MAX(tep.cdt) AS cdt
    FROM
      tbl_express_package tep
    GROUP BY tep.cid,
      tep.recvaddressid) t1
  WHERE t1.cid = 73
  ORDER BY t1.cnt,
    t1.cdt DESC
  LIMIT 1) t1
  LEFT JOIN tbl_customer tc
    ON (tc.id = t1.cid);


十三、​​​​​​​​​​​​​​常用手机号运营商

SELECT
  t1.cid,
  t1.recvaddressid,
  t1.cnt,
  tc.name,
  (case cast(substr(tc.tel,1,3) as INT)
  when 134 then '移动'
   when 135 then '移动'
   when 136 then '移动'
   when 137 then '移动'
   when 138 then '移动'
   when 139 then '移动'
   when 147 then '移动'
   when 150 then '移动'
   when 151 then '移动'
   when 152 then '移动'
   when 157 then '移动'
   when 158 then '移动'
   when 159 then '移动'
   when 182 then '移动'
   when 187 then '移动'
   when 188 then '移动'
   when 130 then '联通'
   when 131 then '联通'
   when 132 then '联通'
   when 155 then '联通'
   when 156 then '联通'
   when 185 then '联通'
   when 186 then '联通'
   when 133 then '电信'
   when 153 then '电信'
   when 180 then '电信'
   when 189 then '电信'
   else '未知' end) AS tel
FROM
  (SELECT
    t1.cid,
    t1.recvaddressid,
    t1.cnt
  FROM
    (SELECT
      tep.cid,
      tep.recvaddressid,
      COUNT(tep.recvaddressid) AS cnt,
      MAX(tep.cdt) AS cdt
    FROM
      tbl_express_package tep
    GROUP BY tep.cid,
      tep.recvaddressid) t1
  WHERE t1.cid = 73
  ORDER BY t1.cnt,
    t1.cdt DESC
  LIMIT 1) t1
  LEFT JOIN tbl_customer tc
    ON (tc.id = t1.cid);


十四、不同手机号数量

SELECT
  tc.id,
  tc.name,
  COUNT(DISTINCT (tc.mobile))
FROM
  tbl_consumer_sender_info tcsi
  LEFT JOIN tbl_customer tc
    ON tc.id = tcsi.ciid
GROUP BY tc.id,
  tc.name,
  tc.mobile;
【版权声明】本文为华为云社区用户原创内容，转载时必须标注文章的来源（华为云社区）、文章链接、文章作者等基本信息， 否则作者和本社区有权追究责任。如果您发现本社区中有涉嫌抄袭的内容，欢迎发送邮件进行举报，并提供相关证据，一经查实，本社区将立刻删除涉嫌侵权内容，举报邮箱： cloudbbs@huaweicloud.com
大数据 智慧物流
  • 点赞
  • 收藏
  • 关注作者

评论（0）

0/1000
抱歉，系统识别当前为高风险访问，暂不支持该操作

全部回复

上滑加载中

设置昵称

在此一键设置昵称，即可参与社区互动！

*长度不超过10个汉字或20个英文字符，设置后3个月内不可修改。

*长度不超过10个汉字或20个英文字符，设置后3个月内不可修改。

确认 取消

加入云驻计划，成为创作者

  • 华为云周边好礼
  • 免费体验产品
  • 特殊身份标识
  • 线下官方门票
  • 内部专家零距离
  • 与10000+优质创作者共同成长
立即加入