编辑：
我将研究toppk提出的解决方案。
我如何调整我的 SELECT 语句来做到这一点？
我想合并每月账单报告中的匹配行，当它们用于同一任务时合并条目。脚本从我的支持票中提取 MySQL 查询并转储到 csv。通常我会给出上个月的时间范围。
许多任务都有多个时间条目，最好将它们合并到一行中。
该脚本按计费客户排序，然后按任务排序，生成最终报告的 .CSV。出于隐私考虑，我已编辑了该示例，但真实报告中的计费总额是正确的。
Desired result from this sample output:
- combine the three entries for ticket 8732 (audio system) into a single 630m (10.5h) line
- combine ticket 8789 (cabinet meeting) into one 120m line
- combine ticket 8182 (backups) into a single 240m line
et cetera
示例 .CSV 摘录：列出：客户端；票名；票号;时间（米）；时间（小时）；我们的内部部门标签
"client","subject","#","time","hours","status","dept"
,,,,,
"museum","audio system: reconfigure and test","8732","30","0.5","closed","Production"
"museum","audio system: reconfigure and test","8732","210","3.5","closed","Production"
"museum","audio system: reconfigure and test","8732","390","6.5","closed","Production"
"museum","documentary premiere in gallery","8733","240","4.0","closed","Production"
"museum","audio and Lectern support","8767","30","0.5","closed","IT_Support"
"museum","County manager cabinet meeting","8789","30","0.5","closed","IT_Support"
"museum","County manager cabinet meeting","8789","90","1.5","closed","IT_Support"
"museum","mass file duplication","8834","45","0.75","closed","IT_Support"
"museum","audio system support","8835","45","0.75","closed","IT_Support"
"museum","PC browser support","8836","45","0.75","closed","IT_Support"
"museum","audio system issues","8840","30","0.5","closed","IT_Support"
"museum","equipment move","8871","75","1.25","closed","IT_Support"
,,,1335,22.25,hours
,,,,,
,,,,,
"dental office","ongoing: manual Eaglesoft backup","8182","30","0.5","open","IT_Support"
"dental office","ongoing: manual Eaglesoft backup","8182","30","0.5","open","IT_Support"
"dental office","ongoing: manual Eaglesoft backup","8182","30","0.5","open","IT_Support"
"dental office","ongoing: manual Eaglesoft backup","8182","30","0.5","open","IT_Support"
"dental office","ongoing: manual Eaglesoft backup","8182","30","0.5","open","IT_Support"
"dental office","ongoing: manual Eaglesoft backup","8182","30","0.5","open","IT_Support"
"dental office","ongoing: manual Eaglesoft backup","8182","30","0.5","open","IT_Support"
"dental office","ongoing: manual Eaglesoft backup","8182","30","0.5","open","IT_Support"
"dental office","failed monitor support","8724","30","0.5","closed","IT_Support"
"dental office","backups server crash","8726","135","2.25","closed","IT_Support"
"dental office","backups server crash","8726","75","1.25","closed","IT_Support"
"dental office","hypervisor virtual backups","8730","120","2","closed","IT_Support"
"dental office","panoramic x-ray access issue","8734","105","1.75","closed","IT_Support"
"dental office","unusual phone behavior / call quality issues","8744","75","1.25","closed","IT_Support"
"dental office","server room power issue","8752","75","1.25","closed","IT_Support"
"dental office","Eaglesoft error","8759","30","0.5","closed","IT_Support"
"dental office","server issue: filesystem management","8761","75","1.25","closed","IT_Support"
"dental office","server room power issue","8780","45","0.75","closed","IT_Support"
"dental office","Eaglesoft schedule problem","8782","60","1","closed","IT_Support"
"dental office","PC power problem","8865","105","1.75","closed","IT_Support"
,,,1290,21.50,hours
,,,,,
,,,,,
,,Total,13125 m,218.75,hours
（因此博物馆部分最终将有九行输出，而不是十二行）
下面是 SELECT 语句——通过 awk 和 sed 管道传输 CSV。
/usr/bin/mysql osticket -Be "SELECT
ost_organization.name as 'client', ost_ticket__cdata.subject, ost_ticket.ticket_id as '#', ost_thread_entry.time_spent as 'time', ost_thread_entry.time_type as 'how', ost_ticket_status.state as status, ost_department.name as dept
FROM ost_organization
JOIN ost_user
ON ost_organization.id = ost_user.org_id
JOIN ost_ticket
ON ost_user.id = ost_ticket.user_id
JOIN ost_ticket_status
on ost_ticket_status.id = ost_ticket.status_id
JOIN ost_ticket__cdata
ON ost_ticket.ticket_id = ost_ticket__cdata.ticket_id
JOIN ost_thread
ON ost_thread.object_id = ost_ticket.ticket_id
JOIN ost_department
ON ost_department.id = ost_ticket.dept_id
JOIN ost_thread_entry
ON ost_thread_entry.thread_id = ost_thread.id
AND ost_thread_entry.time_type != 7
AND ost_thread_entry.time_bill = 1
AND ost_thread_entry.staff_id = $MyAgentID
AND ost_thread_entry.time_spent != 0 and ost_thread_entry.created regexp '$Today'
ORDER BY $SortFlag
;" | awk -F" " 'BEGIN{OFS=FS};{print $1,$2,$3,$4,($4/60),$6,$7}' | sed 's/^/"/;s/$/"/;s/ /","/g'
所以我需要计算 [ost_thread_entry.time_spent] 的总和...但仅在 [ost_ticket.ticket_id] 相同的情况下。 我如何调整这个 SELECT 语句来做到这一点？
笔记：
- “$Today”变量是我作为 CLI 参数提供给脚本的任何日期（例如上个月的 2022-08）。默认为[今天]，例如2022-09-06。
- 票务系统是 OS Ticket，带有用于时间跟踪的第 3 方 mod。
- 数据库以分钟为单位存储时间——脚本根据该时间计算小时。
- 我的第一个倾向是使用类似于下面的 awk，但这需要脚本首先评估重复的行，这让我觉得效率低得多。我的直觉告诉我有一个更智能的数据库解决方案。
awk -F '","' '$3 == 8182 {print $4}' report.csv
谢谢阅读