首页
学习
活动
专区
工具
TVP
发布
社区首页 >问答首页 >在一周/一个月中的每一天按持续时间汇总任务?(PoSH)

在一周/一个月中的每一天按持续时间汇总任务?(PoSH)
EN

Stack Overflow用户
提问于 2018-06-09 02:05:24
回答 1查看 60关注 0票数 3

我正在从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)

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

虽然数据将跨越几周,但我只是想做一份每周报告,这样我就可以很容易地将其转录到我们的time应用程序中(因为他们不会给我API密钥)。所以我会先做一些像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      

我该怎么做呢?我假设是group-object,但不清楚您将如何进行轴心或分组。

EN

回答 1

Stack Overflow用户

回答已采纳

发布于 2018-06-10 21:14:00

用谷歌搜索PowerShellPivot,我发现这个gist.github.com有一种更通用的方法来创建PivotTable。

要转置(交换x,y),只需更改变量$rotate$keep

它还有一个额外的好处,那就是计算行合计

## Q:\Test\2018\06\09\PivotTable.ps1
## Source https://gist.github.com/andyoakley/1651859
# #############################################################################
# Rotates a vertical set similar to an Excel PivotTable 
# #############################################################################
$OutputFile = "MyPivot.csv"

$data = @'
taskname,start,duration
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 |Select-Object taskname, duration, @{n='start';e={($_.start -split ' ')[0]}}

# Fields of interest
$rotate = "taskname"  # Bits along the top
$keep   = "start"     # Those along the side
$value  = "duration"  # What to total

#-------------------- No need to change anything below ------------------------

# Creatre variable to store the output
$rows = @()

# Find the unique "Rotate" [top row of the pivot] values and sort ascending
$pivots = $data | select -unique $rotate | foreach { $_.$rotate} | Sort-Object

# Step through the original data...
#  for each of the "Keep" [left hand side] find the Sum of the "Value" for each "Rotate"

$data | 
    group $keep  | 
    foreach { 
        $group = $_.Group 
        # Create the data row and name it as per the "Keep"
        $row = new-object psobject
        $row | add-member NoteProperty $keep $_.Name 
        # Cycle through the unique "Rotate" values and get the sum
        foreach ($pivot in $pivots) {
            $row | add-member NoteProperty $pivot ($group | where { $_.$rotate -eq $pivot } | measure -sum $value).Sum
        }
        # Add the total to the row
        $row | add-member NoteProperty Total ($group | measure -sum $value).Sum
        # Add the row to the collection 
        $rows += $row
    } 

# Do something with the pivot rows
$rows | Format-Table
$rows | Export-Csv $OutputFile -NoTypeInformation

示例输出:

start    TaskA TaskB Total
-----    ----- ----- -----
6/5/2018   312   200   512
6/6/2018   300         300

或x/y交换

taskname 6/5/2018 6/6/2018 Total
-------- -------- -------- -----
TaskA         312      300   612
TaskB         200            200
票数 1
EN
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接:

https://stackoverflow.com/questions/50766209

复制
相关文章

相似问题

领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档