56,677
社区成员
发帖
与我相关
我的任务
分享
BEGIN
/*
** Step 1
** 名称:Temp
** 参数:无
*/
DECLARE
#a date DEFAULT DATE_SUB(CURDATE(),INTERVAL 1 DAY); # "2017-11-12"
a date DEFAULT "2018-11-12";
DECLARE staffds INT DEFAULT 18000 ; -- 默认当日在线时长超过5小时即为店员
DECLARE outshops INT DEFAULT 0 ; -- 默认当日在线时长少过60秒的即为店外流量
WHILE
a < CURDATE( ) DO
INSERT INTO shuibei_lbs.LBS_MessData ( TransactionDate, shop_id, ApMac, ClientMac, ClientDeviceBrand, EnterTime, Exittime, Inshop, DurationSecond ) SELECT
DATE( A.EnterTime ) AS TransactionDate,
# B.remarks,
B.shop_id,
upper( A.EnterSensorMac ) AS ApMac,
Upper( A.ClientMac ),
"" AS ClientDeviceBrand,
A.EnterTime,
A.ExitTime,
CASE
WHEN A.Rssi >= B.device_rssi
THEN
1 ELSE 0
END,
TIME_TO_SEC( TIMEDIFF( A.ExitTime, A.EnterTime ) ) AS DurationBySecond
FROM
shuibei_lbs.LBS_CustomerRegionEvent A,
shuibei_lbs.shop_device B
WHERE
DATE_FORMAT( A.EnterTime, "%Y-%m-%d" ) = a
AND upper( A.EnterSensorMac ) = B.device_mac
# AND A.Rssi >= B.device_limit_rssi
#and TIME_TO_SEC( TIMEDIFF( A.ExitTime, A.EnterTime ) ) > outshops
ORDER BY
EnterTime;
-- 更新设备品牌
UPDATE shuibei_lbs.LBS_MessData A,
shuibei_lbs.oui B
SET A.ClientDeviceBrand = LEFT ( B.CompanyName, 200 )
WHERE
upper( LEFT ( A.ClientMac, 8 ) ) = B.MAC
AND TransactionDate = a;
-- 筛选出职员清单
INSERT INTO shuibei_lbs.StaffList (ClientMac)
SELECT
distinct TempT.ClientMac
from (SELECT TransactionDate,shop_id,ClientMac, sum(DurationSecond) DS
FROM shuibei_lbs.LBS_MessData
where TransactionDate = a
group by TransactionDate, shop_id, ClientMac
order by TransactionDate, shop_id, ClientMac) TempT
WHERE TempT.DS >= staffds
and TempT.ClientMac not in (select distinct ClientMac from shuibei_lbs.StaffList);
SET a = DATE_ADD( a, INTERVAL 1 DAY );
END WHILE;
END