編集する：
toppkが提案した解決策を見てみましょう。
これを行うために、SELECTステートメントをどのように調整しますか？
毎月の支払いレポートで一致する行をマージし、同じ操作のアイテムをマージしたいと思います。スクリプトはサポートチケットからMySQLクエリを抽出してcsvにダンプします。通常、私は前月の期間を提供します。
多くのタスクには複数の時間項目があるため、これを1行にまとめるのが最善です。
スクリプトは、請求顧客に基づいてソートし、操作に基づいて最終レポートの.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
（それで博物館セクションは12行ではなく9行の出力で終わります）
以下は、awkとsedを介してパイプされたCSVであるSELECTステートメントです。
/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チケットです。
- データベースは時間を分単位で保存します。スクリプトはこの時間に基づいて時間を計算します。
- 私の最初の傾向は以下のようなawkを使用することですが、これはスクリプトが最初に重複した行を評価する必要があるため、はるかに効率的ではないと思います。私の直感は、よりスマートなデータベースソリューションがあると言います。
awk -F '","' '$3 == 8182 {print $4}' report.csv
読んでくれてありがとう