SQLlite strftime 函数按月获取分组数据 [英] SQLlite strftime function to get grouped data by months
本文介绍了SQLlite strftime 函数按月获取分组数据的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有以下结构和数据的表:
i have table with following structure and data:
例如,我想在给定日期范围内按月分组数据(从 2014-01-01 到 2014-12-31).无法获得某些月份的数据,但我仍然需要在结果信息中提供给定月份的结果 0.
I would like to get grouped data by months in given date range for example (from 2014-01-01 to 2014-12-31). Data for some months cannot be available but i still need to have in result information that in given month is result 0.
结果应具有以下格式:
MONTH | DIALS_CNT | APPT_CNT | CONVERS_CNT | CANNOT_REACH_CNT |
2014-01 | 100 | 50 | 20 | 30 |
2014-02 | 100 | 40 | 30 | 30 |
2014-03 | 0 | 0 | 0 | 0 |
etc..
哪里
APPT_CNT = WHERE call.result = APPT
CONVERS_CNT = WHERE call.result = CONV_NO_APPT
CANNOT_REACH_CNT = WHERE call.result = CANNOT_REACH
我该如何使用strftime 功能来实现?
How can i do it please with usage function strftime ?
非常感谢任何帮助或示例.
Many thanks for any help or example.
推荐答案
SELECT Month,
(SELECT COUNT(*)
FROM MyTable
WHERE date LIKE Month || '%'
) AS Dials_Cnt,
(SELECT SUM(Call_Result = 'APPT')
FROM MyTable
WHERE date LIKE Month || '%'
) AS Appt_Cnt,
...
FROM (SELECT '2014-01' AS Month UNION ALL
SELECT '2014-02' UNION ALL
SELECT '2014-03' UNION ALL
...
SELECT '2014-12')
这篇关于SQLlite strftime 函数按月获取分组数据的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文