CREATE TABLE date_store_stat ( id bigint(20) NOT NULL AUTO_INCREMENT, data_date varchar(16) NOT NULL COMMENT ‘日期’, vid int(11) NOT NULL COMMENT ‘smr_visit的id’, sid int(11) NOT NULL COMMENT ‘店铺ID’, cid varchar(32) NOT NULL COMMENT ‘访问CID’, pid int(11) DEFAULT NULL COMMENT ‘SkuID’, gid int(11) DEFAULT NULL COMMENT ‘商品ID’, gname varchar(365) DEFAULT NULL COMMENT ‘商品名称’, region varchar(32) NOT NULL DEFAULT ‘其他’ COMMENT ‘省份’, iscopy int(11) NOT NULL DEFAULT ‘0’ COMMENT ‘是否为体面数据,0:真数据;1:体面数据’,
PRIMARY KEY (id),
KEY idx_dssi_data_date (data_date),
KEY idx_dssi_gid (gid),
KEY idx_dssi_iscopy (iscopy),
KEY idx_dssi_sid (sid)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin
查询sql：
SELECT a.data_date,a.sid,COUNT(DISTINCT a.cid) ‘allcount’,COUNT(DISTINCT IF(a.pid IS NULL,a.cid,NULL)) ‘scount’
FROM date_store_stat a WHERE a.data_date=‘2021-07-24’ GROUP BY a.sid ORDER BY a.data_date
SELECT a.data_date,a.sid,COUNT(DISTINCT a.cid) ‘allcount’,COUNT(DISTINCT IF(a.pid IS NULL,a.cid,NULL)) ‘scount’
FROM date_store_stat a
WHERE a.sid = 519162 AND a.data_date=‘2021-07-24’
GROUP BY a.data_date
