我想订购这个查询,并获得本周每天的机票金额,结果如下:
WEEK_DAY CREATED_TICKETS
MONDAY 3
TUESDAY 5
WEDNESDAY 0
FRIDAY 2
SELECT
TO_CHAR(TRUNC(CREATED_AT), 'DAY') AS WEEK_DAY,
COUNT(ID) as CREATED_TICKETS
FROM
FRESHDESK_API
WHERE
TO_CHAR(TRUNC(CREATED_AT), 'IW') = TO_CHAR(TRUNC(SYSDATE), 'IW')
GROUP BY
TO_CHAR(TRUNC(CREATED_AT), 'DAY')
ORDER BY
TO_CHAR(TRUNC(CREATED_AT), 'DAY') ASC
我建议:
select to_char(created_at, 'day') as week_day, count(*) as created_tickets
from freshdesk_api
where created_at >= trunc(sysdate, 'iw') and created_at < trunc(sysdate, 'iw') + 7
group by to_char(created_at, 'day')
order by min(created_at)
诀窍是在order by
子句中使用适用于date
列的聚合函数。
查询的其他更改:
我优化了
where
子句,因此在要筛选的列上不应用日期函数;这更有效(有人说谓词是SARGable(假定称为
id
的东西是不可为null的,因此count(*)
等效于count(id)
(并且更高效,因为数据库不需要null
检查每个值(不需要嵌套CCD_ 8和CCD_;
to_char()
在这里就足够了,因为你只关心白天的部分