Skip to content

Instantly share code, notes, and snippets.

@peytonyip

peytonyip/user.sql

Last active November 20, 2020 07:39
Show Gist options
  • Save peytonyip/f4d6c7b752766b87bc350d595dab6204 to your computer and use it in GitHub Desktop.
Save peytonyip/f4d6c7b752766b87bc350d595dab6204 to your computer and use it in GitHub Desktop.
Download ZIP
[SQL] 记录常用的 sql 命令 #sql
Raw
user.sql
select * from test;
Raw
统计ai_service相关.sql
-- 尽量避免在系统进行外呼的时候进行查询--
--机器人拨打次数--
SELECT COUNT(A.phone),B.project_id,B.project_name
FROM call_out_customer_detail AS A
LEFT JOIN
(
SELECT auto_call_out.title AS title, auto_call_out.id AS id,project_id,project.`name` AS project_name
FROM auto_call_out
LEFT JOIN project
ON auto_call_out.project_id = project.id
) AS B
ON B.id = A.task_id
WHERE A.update_time BETWEEN '2020-10-23 00:00:00' AND '2020-11-05 23:59:59'
AND `status` = 4
GROUP BY B.project_id
结果：1491500
--接通次数--
SELECT COUNT(A.phone),B.project_id,B.project_name
FROM call_out_customer_detail AS A
LEFT JOIN
(
SELECT auto_call_out.title AS title, auto_call_out.id AS id,project_id,project.`name` AS project_name
FROM auto_call_out
LEFT JOIN project
ON auto_call_out.project_id = project.id
) AS B
ON B.id = A.task_id
LEFT JOIN call_log AS C
ON C.id = A.call_log_id
WHERE A.update_time BETWEEN '2020-10-23 00:00:00' AND '2020-11-05 23:59:59'
AND A.`status` = 4
AND A.call_log_id <> 0
AND C.call_duration > 0
GROUP BY B.project_id
结果：240645
#查询通话的人数：
#机器人外呼
SELECT COUNT(DISTINCT phone) FROM call_out_customer_detail
WHERE update_time BETWEEN '2020-08-01 00:00:00' AND '2020-08-31 23:59:59'
AND `status` = 4
AND call_log_id <> 0;
8月：39443
9月：78415
10月：258042
#门店外呼
SELECT COUNT(DISTINCT phone) FROM call_out
WHERE update_time BETWEEN '2020-08-01 00:00:00' AND '2020-08-31 23:59:59'
AND flag = 1
AND call_situation <> 3;
8月：0
9月：10
10月：6737
#查询通话时长
#门店外呼
SELECT
SUM(l.call_duration)
FROM
call_out AS c
INNER JOIN call_log l ON c.call_log_id = l.id
AND l.call_start_time BETWEEN '2020-08-01 00:00:00'
AND '2020-08-31 23:59:59'
WHERE
c.call_log_id <> 0
AND c.flag = 1
AND c.call_situation <> 3;
8月：0
9月：19139
10月：311183
#机器人外呼时长
SELECT
SUM(l.call_duration) AS 总通话时长
FROM
call_out_customer_detail AS d
INNER JOIN call_log l ON d.call_log_id = l.id
AND l.call_start_time BETWEEN '2020-08-01 00:00:00'
AND '2020-08-31 23:59:59'
WHERE
d.`status` = 4
AND d.call_log_id <> 0;
8月：1346171
9月：2276623
10月：4304372
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment