powershell - 按一周/一个月中每一天的持续时间汇总任务？ (PoSH)

Question

我正在从 Web 服务解析 JSON 以获取我的任务(使用 TimeFlip)。现在，我返回每个任务、发生时间和持续时间，因此数据如下所示:

(taskname, start, durationinSec)
TaskA,"6/5/2018 12:16:36 PM",312
TaskB,"6/5/2018 12:30:36 PM",200
TaskA,"6/6/2018 08:00:00 AM",150
TaskA,"6/6/2018 03:00:00 PM",150
(etc etc)

我想生成一个汇总报告，按天显示哪些任务有多少时间。

虽然数据会跨越数周，但我只是想做一份每周报告，我可以轻松地将其转录到我们的时间应用程序中(因为他们不会给我 API key )。所以我会做类似 where {$_.start -gt (? {$_.start -gt (get-date -Hour 0 -Minute 00 -Second 00).adddays(-7)}首先。

       6/5/2018    6/6/2018
 TaskA 312         300
 TaskB 200      

我该怎么做？我假设组对象，但不清楚您将如何进行数据透视甚至分组。

Answer 1

以下不输出数据透视表，但执行所需的分组和聚合:

$rows = @'
taskname,start,durationinSec
TaskA,"6/5/2018 12:16:36 PM",312
TaskB,"6/5/2018 12:30:36 PM",200
TaskA,"6/6/2018 08:00:00 AM",150
TaskA,"6/6/2018 03:00:00 PM",150
'@ | ConvertFrom-Csv

$rows | Group-Object { (-split $_.start)[0] }, taskname | ForEach-Object {
  $_ | Select-Object @{ n='Date'; e={$_.Values[0]} }, 
                     @{ n='Task'; e={$_.Values[1]} }, 
                     @{ n='Duration'; e={ ($_.Group | Measure-Object durationInSec -Sum).Sum } } 
}

(-split $_.start)[0] 按空格拆分每个 start 值并返回第一个标记 ([0] )，这是时间戳的日期部分；例如，6/5/2018 返回 6/5/2018 12:16:36 PM；将此操作作为脚本 block ({ ... }) 传递给 Group-Object 意味着分组仅按日期发生，而不是时间(除了按 任务名)。

这会产生:

Date     Task  Duration
----     ----  --------
6/5/2018 TaskA      312
6/5/2018 TaskB      200
6/6/2018 TaskA      300

---

构建类似数据透视表的输出需要更多的努力，而且不会很快:

假设 $objs 包含上面创建的对象 ($objs = $rows | Group-Object ...)。

# Get all distinct dates.
$dates = $objs | Select-Object -Unique -ExpandProperty Date
# Get all distinct tasks.
$tasks = $objs | Select-Object -Unique -ExpandProperty Task

# Create an ordered hashtable that contains an entry for each task that
# holds a nested hashtable with (empty-for-now) entries for all dates.
$ohtPivot = [ordered] @{}
$tasks | ForEach-Object {
  $ohtDates = [ordered] @{}
  $dates | ForEach-Object { $ohtDates[$_] = $null }
  $ohtPivot[$_] = $ohtDates
}

# Fill the hashtable from the grouped objects with the task- and 
# date-specific durations.
$objs | ForEach-Object { $ohtPivot[$_.Task][$_.Date] = $_.Duration }

# Output the resulting hashtable in pivot-table-like form by transforming
# each entry into a custom object
$ohtPivot.GetEnumerator() | ForEach-Object {
  [pscustomobject] @{ Task = $_.Key } | Add-Member -PassThru -NotePropertyMembers $_.Value
}

以上结果:

Task  6/5/2018  6/6/2018
----  --------  --------
TaskA      312      300
TaskB      200