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

2022-12-19 15:46:36 浏览数 (1)

​用户标签开发

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

代码语言：javascript复制
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;

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

代码语言：javascript复制
----用户首单收货地址
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

三、用户首单来源

代码语言：javascript复制
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;

四、用户首单省份

代码语言：javascript复制
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;

五、用户首单城市

代码语言：javascript复制
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);

六、用户首单地区

代码语言：javascript复制
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;

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

代码语言：javascript复制
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;

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

代码语言：javascript复制
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);

九、常用收货省份

代码语言：javascript复制
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);

十、常用收货地区

代码语言：javascript复制
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);

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

代码语言：javascript复制
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);

十二、常用手机号码

代码语言：javascript复制
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);

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

代码语言：javascript复制
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);

十四、不同手机号数量

代码语言：javascript复制
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;
大数据处理套件TBDS 大数据解决方案大数据

0 人点赞