如何修改SQL统计已接电话前1小时内同主叫及企业的未接呼叫次数

问题描述 #

现有answered（已接呼叫）和unanswered（未接呼叫）两张表，需要统计每个已接呼叫对应的、满足以下条件的未接呼叫次数：

• 与已接呼叫拥有相同的callingNumber和company
• 未接呼叫时间在已接呼叫时间的前1小时以内

当前查询未做时间范围过滤，导致统计了超出1小时的未接记录，需要修改SQL以实现需求。

表结构与测试数据 #

DROP TABLE IF EXISTS answered;
DROP TABLE IF EXISTS unanswered;

CREATE TABLE answered (
    ttime varchar(255),
    calledNumber varchar(255),
    callingNumber varchar(255),
    attendantNumber varchar(255),
    identifier varchar(255),
    company varchar(255),
    key12 varchar(255)
);

CREATE TABLE unanswered (
    ttime varchar(255),
    calledNumber varchar(255),
    callingNumber varchar(255),
    attendantNumber varchar(255),
    identifier varchar(255),
    company varchar(255),
    key12 varchar(255)
);

INSERT INTO answered (ttime, calledNumber, attendantNumber, callingNumber, identifier, company, key12) VALUES
('2022-12-19 04:13:00', '+123456',  '+2345672',  '+3234343', 'abcdefg212', '23343', 'Answered'),
('2022-12-19 04:37:00', '+123232',  '+2345671',  '+3234343', 'abcdefg213', '23343', 'Answered'),
('2022-12-19 06:47:00', '+127853',  '+2345671',  '+3231237', 'abcdefg214', '23344', 'Answered'),
('2022-12-19 18:30:00', '+566312',  '+1212212',  '+3231223', 'abcdefg222', '23345', 'Answered');

INSERT INTO unanswered (ttime, calledNumber, attendantNumber, callingNumber, identifier, company, key12) VALUES
('2022-12-19 04:23:00', '+123456',  '+2345672',  '+3234343', 'abcdefg215', '23343', 'Unanswered'),
('2022-12-19 04:24:00', '+123232',  '+2345671',  '+3234343', 'abcdefg216', '23343', 'Unanswered'),
('2022-12-19 06:27:00', '+127853',  '+2345671',  '+3231237', 'abcdefg217', '23344', 'Unanswered'),
('2022-12-19 17:47:00', '+127854',  '+2345676',  '+3231223', 'abcdefg218', '23345', 'Unanswered'),
('2022-12-19 17:07:00', '+127854',  '+1234216',  '+3231223', 'abcdefg219', '23345', 'Unanswered'),
('2022-12-19 12:47:00', '+125732',  '+9845646',  '+3231223', 'abcdefg220', '23345', 'Unanswered'),
('2022-12-19 13:47:00', '+127583',  '+1231221',  '+3231223', 'abcdefg221', '23345', 'Unanswered');

当前查询 #

WITH
    t1 AS (
        SELECT DISTINCT 
            ttime,
            calledNumber, 
            attendantNumber,
            callingNumber,
            identifier,
            company,
            key12
        FROM answered 
    ),
    t2 AS (
        SELECT DISTINCT 
            ttime,
            calledNumber, 
            '' as attendantNumber,
            callingNumber,
            identifier,
            company,
            key12
        FROM unanswered 
    ),
t3 AS (
        SELECT ttime, calledNumber, attendantNumber, callingNumber, identifier, company, key12
        FROM t1
        UNION ALL
        SELECT ttime, calledNumber, attendantNumber, callingNumber, identifier, company, key12
        FROM t2
      ),
t4 AS (
        SELECT row_number() OVER (PARTITION BY callingNumber, company ORDER BY ttime) as row_numb,
            ttime, calledNumber, attendantNumber, callingNumber, identifier, company, key12
        FROM t3
      ),
t5 AS (
        SELECT row_numb, ttime, calledNumber, attendantNumber, callingNumber, identifier, company, key12,
            lag(row_numb, 1, 0) OVER (PARTITION BY callingNumber, company ORDER BY ttime) as info1
        FROM t4
        WHERE key12 = 'Answered'
      ),
t6 AS (
        SELECT ttime, calledNumber, attendantNumber, callingNumber, identifier, company, key12,
        CASE 
            WHEN (row_numb-info1) = 1 THEN '1 attempt until answered'
            WHEN (row_numb-info1) = 2 THEN '2 attempt until answered'
            ELSE '3 attempt or more until answered'
        END AS key122
        FROM t5
      )
SELECT * FROM t6
ORDER BY ttime 

当前输出 #

ttime, calledNumber, attendantNumber, callingNumber, identifier, company, key12, key122

'2022-12-19 04:13:00', '+123456', '+2345672', '+3234343', 'abcdefg212', '23343', 'Answered', '1 attempt until answered'

'2022-12-19 04:37:00', '+123232', '+2345671', '+3234343', 'abcdefg213', '23343', 'Answered', '3 attempt or more until answered'

'2022-12-19 06:47:00', '+127853', '+2345671', '+3231237', 'abcdefg214', '23344', 'Answered', '2 attempt until answered'

'2022-12-19 18:30:00', '+566312', '+1212212', '+3231223', 'abcdefg222', '23345', 'Answered', '3 attempt or more until answered' 

期望输出 #

ttime, calledNumber, attendantNumber, callingNumber, identifier, company, key12, key122

'2022-12-19 04:13:00', '+123456', '+2345672', '+3234343', 'abcdefg212', '23343', 'Answered', '1 attempt until answered'

'2022-12-19 04:37:00', '+123232', '+2345671', '+3234343', 'abcdefg213', '23343', 'Answered', '3 attempt or more until answered'

'2022-12-19 06:47:00', '+127853', '+2345671', '+3231237', 'abcdefg214', '23344', 'Answered', '1 attempt until answered'

'2022-12-19 18:30:00', '+566312', '+1212212', '+3231223', 'abcdefg222', '23345', 'Answered', '2 attempt or more until answered' 

需要排除的未接记录 #

以下未接呼叫因超出已接呼叫前1小时范围，不应被统计：

('2022-12-19 17:07:00', '+127854',  '+1234216',  '+3231223', 'abcdefg219', '23345', 'Unanswered'),
('2022-12-19 12:47:00', '+125732',  '+9845646',  '+3231223', 'abcdefg220', '23345', 'Unanswered'),
('2022-12-19 13:47:00', '+127583',  '+1231221',  '+3231223', 'abcdefg221', '23345', 'Unanswered'),
('2022-12-19 05:27:00', '+127853',  '+2345671',  '+3231237', 'abcdefg217', '23344', 'Unanswered'),

修改后的SQL #

WITH
    -- 将已接呼叫的时间转换为datetime类型，方便计算
    answered_with_dt AS (
        SELECT 
            *,
            STR_TO_DATE(ttime, '%Y-%m-%d %H:%i:%s') AS dt
        FROM answered
    ),
    -- 筛选出符合时间范围的未接呼叫：同callingNumber、company，且在已接呼叫前1小时内
    valid_unanswered AS (
        SELECT DISTINCT
            u.ttime,
            u.calledNumber,
            '' AS attendantNumber,
            u.callingNumber,
            u.identifier,
            u.company,
            u.key12
        FROM unanswered u
        JOIN answered_with_dt a 
            ON u.callingNumber = a.callingNumber 
            AND u.company = a.company
            AND STR_TO_DATE(u.ttime, '%Y-%m-%d %H:%i:%s') >= DATE_SUB(a.dt, INTERVAL 1 HOUR)
            AND STR_TO_DATE(u.ttime, '%Y-%m-%d %H:%i:%s') < a.dt
    ),
    -- 合并已接和有效未接记录
    combined AS (
        SELECT 
            ttime,
            calledNumber,
            attendantNumber,
            callingNumber,
            identifier,
            company,
            key12
        FROM answered_with_dt
        UNION ALL
        SELECT 
            ttime,
            calledNumber,
            attendantNumber,
            callingNumber,
            identifier,
            company,
            key12
        FROM valid_unanswered
    ),
    -- 按callingNumber和company分组排序，生成行号
    ranked AS (
        SELECT 
            ROW_NUMBER() OVER (PARTITION BY callingNumber, company ORDER BY STR_TO_DATE(ttime, '%Y-%m-%d %H:%i:%s')) AS row_numb,
            ttime,
            calledNumber,
            attendantNumber,
            callingNumber,
            identifier,
            company,
            key12
        FROM combined
    ),
    -- 仅保留已接记录，并获取上一条已接记录的行号
    answered_ranked AS (
        SELECT 
            row_numb,
            ttime,
            calledNumber,
            attendantNumber,
            callingNumber,
            identifier,
            company,
            key12,
            LAG(row_numb, 1, 0) OVER (PARTITION BY callingNumber, company ORDER BY STR_TO_DATE(ttime, '%Y-%m-%d %H:%i:%s')) AS prev_answered_row
        FROM ranked
        WHERE key12 = 'Answered'
    )
-- 计算未接次数并生成结果
SELECT 
    ttime,
    calledNumber,
    attendantNumber,
    callingNumber,
    identifier,
    company,
    key12,
    CASE 
        WHEN (row_numb - prev_answered_row) = 1 THEN '1 attempt until answered'
        WHEN (row_numb - prev_answered_row) = 2 THEN '2 attempt until answered'
        ELSE '3 attempt or more until answered'
    END AS key122
FROM answered_ranked
ORDER BY STR_TO_DATE(ttime, '%Y-%m-%d %H:%i:%s');

修改说明 #

1. 时间类型转换：将ttime字符串转换为datetime类型（使用STR_TO_DATE），便于时间范围计算。
2. 筛选有效未接记录：通过JOIN已接呼叫表，只保留与已接呼叫同callingNumber、company，且时间在已接呼叫前1小时内的未接记录。
3. 合并与排序：仅合并已接记录和有效未接记录，避免引入无效数据干扰行号计算。
4. 行号计算：基于转换后的datetime排序，确保行号顺序准确。

内容的提问来源于stack exchange，提问作者Gonçalo Fontes