Credentials Manager 活动分析-解网

问：

结果应包含以下列：mac/type/started_at/ended_at/activities mac 帐户 MAC 地址类型-序列活动类型序列开始started_at日期和时间 ended_at日期和时间序列结束 activities-序列中的活动数

问题结果应按 mac 升序排序，然后按 started_at 升序排序

结果中只应包含 3 个或更多个连续错误类型操作的序列。下表已经是原始数据。

DROP TABLE IF EXISTS accounts;
DROP TABLE IF EXISTS activities;

-- create accounts table
CREATE TABLE accounts (
  id INTEGER PRIMARY KEY,
  mac INTEGER NOT NULL
);

-- create activities table
CREATE TABLE activities (
  account_id INTEGER NOT NULL,
  dt INTEGER NOT NULL,
  type INTEGER NOT NULL, 
  FOREIGN KEY (account_id) REFERENCES accounts(id)
);
-- insert some accounts values
INSERT INTO accounts VALUES (1, 'C4-85-20-F3-E9-CD');
INSERT INTO accounts VALUES (2, 'CB-5D-05-EE-05-97');
INSERT INTO accounts VALUES (3, '5C-40-A9-D4-25-77');

-- insert some activities values
INSERT INTO activities VALUES (1, 10, 'SUCCESS');
INSERT INTO activities VALUES (1, 11, 'ERROR');
INSERT INTO activities VALUES (1, 12, 'ERROR');
INSERT INTO activities VALUES (1, 13, 'ERROR');
INSERT INTO activities VALUES (1, 14, 'ERROR');
INSERT INTO activities VALUES (1, 15, 'SUCCESS');
INSERT INTO activities VALUES (1, 16, 'ERROR');
INSERT INTO activities VALUES (1, 17, 'ERROR');
INSERT INTO activities VALUES (1, 18, 'ERROR');
INSERT INTO activities VALUES (1, 19, 'SUCCESS');
INSERT INTO activities VALUES (2, 10, 'ERROR');
INSERT INTO activities VALUES (2, 11, 'ERROR');
INSERT INTO activities VALUES (2, 12, 'ERROR');
INSERT INTO activities VALUES (2, 13, 'SUCCESS');
INSERT INTO activities VALUES (2, 14, 'SUCCESS');
INSERT INTO activities VALUES (2, 15, 'SUCCESS');
INSERT INTO activities VALUES (3, 11, 'ERROR');
INSERT INTO activities VALUES (3, 12, 'ERROR');
INSERT INTO activities VALUES (3, 13, 'SUCCESS');
INSERT INTO activities VALUES (3, 14, 'SUCCESS');


SELECT "account_id", COUNT(*), min("dt"), max("dt") FROM activities WHERE type = 'ERROR' 
GROUP BY "account_id"

预期成果：

MAC                 | TYPE | started_at |  ended_at |   total_activities 
C4-85-20-F3-E9-CD | ERROR | 11         | 14        | 4

SQL格式

WITH cte AS(SELECT 
   account_id
  ,dt
,type
  , IF(type <> LAG(type) OVER(ORDER BY account_id,dt) OR LAG(type) OVER(ORDER BY account_id, dt) IS NULL,1,0) sm
FROM activities)
,CTE2 as (
  SELECT 
   account_id
  ,dt
,type
,SUM(sm) OVER(ORDER BY account_id,dt)  grp
  FROM cte)
SELECT
mac,
  
  MIN(dt) as min_dt, MAX(dt) as MAX , count(*) cnt
FROM CTE2 c JOIN accounts a ON c.account_id = a.id
WHERE type = 'ERROR'
GROUP BY grp, mac
HAVING cnt > 3

苹果电脑	min_dt	麦克斯	碳纳米管
分子式：C4-85-20-F3-E9-CD	11	14	4

小提琴

或者可以被其他数据库使用，而不是作为之前的示例，仅适用于MySQL 8.xCASE WHEN

WITH cte AS(SELECT 
   account_id
  ,dt
,type
  , CASE WHEN type <> LAG(type) OVER(ORDER BY account_id,dt) OR LAG(type) OVER(ORDER BY account_id, dt) IS NULL
  THEN 1 ELSE 0 END sm
FROM activities)
,CTE2 as (
  SELECT 
   account_id
  ,dt
,type
,SUM(sm) OVER(ORDER BY account_id,dt)  grp
  FROM cte)
SELECT
mac,
  
  MIN(dt) as min_dt, MAX(dt) as MAX , count(*) cnt
FROM CTE2 c JOIN accounts a ON c.account_id = a.id
WHERE type = 'ERROR'
GROUP BY grp, mac
HAVING cnt > 3

苹果电脑	min_dt	麦克斯	碳纳米管
分子式：C4-85-20-F3-E9-CD	11	14	4

小提琴

Credentials Manager 活动分析

Credentials Manager Activity Analysis

评论

评论