分组、求和和排序

Question

我想要一个按Name，Year/Month和Total的分类。我怎么才能用我目前所拥有的东西做到这一点呢？

我的数据如下所示：

​

|  name   | ArtifactID |           Name            |      DateCollected      | FileSizeInBytes | WorkspaceArtifactId | TimestampOfLatestRecord |
+---------+------------+---------------------------+-------------------------+-----------------+---------------------+-------------------------+
| Pony    |    1265555 | LiteDataPublishedToReview | 2018-12-21 00:00:00.000 | 5474.00         |             2534710 | 2018-12-21 09:26:49.000 |
| Wheels  |    1265566 | LiteDataPublishedToReview | 2019-02-26 00:00:00.000 | 50668.00        |             2634282 | 2019-02-26 17:38:39.000 |
| Wheels  |    1265567 | LiteDataPublishedToReview | 2019-01-11 00:00:00.000 | 10921638320.00  |             2634282 | 2019-01-11 16:44:04.000 |
| Wheels  |    1265568 | LiteDataPublishedToReview | 2019-01-15 00:00:00.000 | 110261521.00    |             2634282 | 2019-01-15 17:43:57.000 |
| Wheels  |    1265569 | LiteDataProcessed         | 2018-12-13 00:00:00.000 | 123187605031.00 |             2634282 | 2018-12-13 21:50:34.000 |
| Wheels  |    1265570 | FullDataProcessed         | 2018-12-13 00:00:00.000 | 6810556609.00   |             2634282 | 2018-12-13 21:50:34.000 |
| Wheels  |    1265571 | LiteDataProcessed         | 2018-12-15 00:00:00.000 | 0.00            |             2634282 | 2018-12-15 14:52:20.000 |
| Wheels  |    1265572 | FullDataProcessed         | 2018-12-15 00:00:00.000 | 13362690.00     |             2634282 | 2018-12-15 14:52:20.000 |
| Wheels  |    1265573 | LiteDataProcessed         | 2019-01-09 00:00:00.000 | 1480303616.00   |             2634282 | 2019-01-09 13:52:23.000 |
| Wheels  |    1265574 | FullDataProcessed         | 2019-01-09 00:00:00.000 | 0.00            |             2634282 | 2019-01-09 13:52:23.000 |
| Wheels  |    1265575 | LiteDataProcessed         | 2019-02-25 00:00:00.000 | 0.00            |             2634282 | 2019-02-25 10:49:41.000 |
| Wheels  |    1265576 | FullDataProcessed         | 2019-02-25 00:00:00.000 | 7633201.00      |             2634282 | 2019-02-25 10:49:41.000 |
| Levack  |    1265577 | LiteDataProcessed         | 2018-12-16 00:00:00.000 | 0.00            |             2636230 | 2018-12-16 10:13:36.000 |
| Levack  |    1265578 | FullDataProcessed         | 2018-12-16 00:00:00.000 | 59202559.00     |             2636230 | 2018-12-16 10:13:36.000 |
| Van     |    1265579 | LiteDataPublishedToReview | 2019-01-11 00:00:00.000 | 2646602711.00   |             2636845 | 2019-01-11 09:50:49.000 |
| Van     |    1265580 | LiteDataPublishedToReview | 2019-01-10 00:00:00.000 | 10081222022.00  |             2636845 | 2019-01-10 18:32:03.000 |
| Van     |    1265581 | LiteDataPublishedToReview | 2019-01-15 00:00:00.000 | 3009227476.00   |             2636845 | 2019-01-15 10:49:38.000 |
| Van     |    1265582 | LiteDataPublishedToReview | 2019-03-26 00:00:00.000 | 87220831.00     |             2636845 | 2019-03-26 10:34:10.000 |
| Van     |    1265583 | LiteDataPublishedToReview | 2019-03-28 00:00:00.000 | 688708119.00    |             2636845 | 2019-03-28 14:11:38.000 |
| Van     |    1265584 | LiteDataProcessed         | 2018-12-18 00:00:00.000 | 5408886887.00   |             2636845 | 2018-12-18 11:29:03.000 |
| Van     |    1265585 | FullDataProcessed         | 2018-12-18 00:00:00.000 | 0.00            |             2636845 | 2018-12-18 11:29:03.000 |
| Van     |    1265586 | LiteDataProcessed         | 2018-12-19 00:00:00.000 | 12535359488.00  |             2636845 | 2018-12-19 17:25:10.000 |
| Van     |    1265587 | FullDataProcessed         | 2018-12-19 00:00:00.000 | 0.00            |             2636845 | 2018-12-19 17:25:10.000 |
| Van     |    1265588 | LiteDataProcessed         | 2018-12-21 00:00:00.000 | 52599693312.00  |             2636845 | 2018-12-21 09:09:18.000 |
| Van     |    1265589 | FullDataProcessed         | 2018-12-21 00:00:00.000 | 0.00            |             2636845 | 2018-12-21 09:09:18.000 |
| Van     |    1265590 | LiteDataProcessed         | 2019-03-25 00:00:00.000 | 3588613120.00   |             2636845 | 2019-03-25 16:41:17.000 |
| Van     |    1265591 | FullDataProcessed         | 2019-03-25 00:00:00.000 | 0.00            |             2636845 | 2019-03-25 16:41:17.000 |
| Holiday |    1265592 | LiteDataProcessed         | 2018-12-28 00:00:00.000 | 0.00            |             2638126 | 2018-12-28 09:15:21.000 |
| Holiday |    1265593 | FullDataProcessed         | 2018-12-28 00:00:00.000 | 9219122847.00   |             2638126 | 2018-12-28 09:15:21.000 |
| Holiday |    1265594 | LiteDataProcessed         | 2019-01-31 00:00:00.000 | 0.00            |             2638126 | 2019-01-31 14:45:07.000 |
| Holiday |    1265595 | FullDataProcessed         | 2019-01-31 00:00:00.000 | 61727744.00     |             2638126 | 2019-01-31 14:45:07.000 |
| Holiday |    1265596 | LiteDataProcessed         | 2019-02-05 00:00:00.000 | 0.00            |             2638126 | 2019-02-05 15:23:27.000 |
| Holiday |    1265597 | FullDataProcessed         | 2019-02-05 00:00:00.000 | 199454805.00    |             2638126 | 2019-02-05 15:23:27.000 |
| Holiday |    1265598 | LiteDataProcessed         | 2019-02-07 00:00:00.000 | 0.00            |             2638126 | 2019-02-07 11:55:55.000 |
| Holiday |    1265599 | FullDataProcessed         | 2019-02-07 00:00:00.000 | 17944713.00     |             2638126 | 2019-02-07 11:55:55.000 |
| Holiday |    1265600 | LiteDataProcessed         | 2019-02-13 00:00:00.000 | 0.00            |             2638126 | 2019-02-13 15:48:56.000 |
| Holiday |    1265601 | FullDataProcessed         | 2019-02-13 00:00:00.000 | 60421568.00     |             2638126 | 2019-02-13 15:48:56.000 |
| Crosbie |    1265604 | LiteDataProcessed         | 2019-01-21 00:00:00.000 | 0.00            |             2644032 | 2019-01-21 15:43:43.000 |
| Crosbie |    1265605 | FullDataProcessed         | 2019-01-21 00:00:00.000 | 131445.00       |             2644032 | 2019-01-21 15:43:43.000 |
| Stone   |    1265606 | LiteDataPublishedToReview | 2019-02-12 00:00:00.000 | 1626943444.00   |             2647518 | 2019-02-12 17:45:25.000 |
| Stone   |    1265607 | LiteDataPublishedToReview | 2019-03-05 00:00:00.000 | 2134872671.00   |             2647518 | 2019-03-05 13:00:31.000 |
| Stone   |    1265608 | LiteDataProcessed         | 2019-02-05 00:00:00.000 | 38828043264.00  |             2647518 | 2019-02-05 09:40:55.000 |
| Stone   |    1265609 | FullDataProcessed         | 2019-02-05 00:00:00.000 | 0.00            |             2647518 | 2019-02-05 09:40:55.000 |
| Frost   |    1265610 | LiteDataPublishedToReview | 2019-03-18 00:00:00.000 | 776025640.00    |             2658542 | 2019-03-18 12:34:10.000 |
| Frost   |    1265611 | LiteDataPublishedToReview | 2019-03-05 00:00:00.000 | 3325335118.00   |             2658542 | 2019-03-05 15:02:39.000 |
| Frost   |    1265612 | LiteDataPublishedToReview | 2019-03-20 00:00:00.000 | 211927893.00    |             2658542 | 2019-03-20 17:25:30.000 |
| Frost   |    1265613 | LiteDataPublishedToReview | 2019-03-06 00:00:00.000 | 466536488.00    |             2658542 | 2019-03-06 11:00:59.000 |
| Frost   |    1265614 | LiteDataPublishedToReview | 2019-03-21 00:00:00.000 | 3863850553.00   |             2658542 | 2019-03-21 17:14:27.000 |
| Frost   |    1265615 | LiteDataProcessed         | 2019-02-28 00:00:00.000 | 94249740012.00  |             2658542 | 2019-02-28 14:13:23.000 |
| Frost   |    1265616 | FullDataProcessed         | 2019-02-28 00:00:00.000 | 0.00            |             2658542 | 2019-02-28 14:13:23.000 |
| Yellow  |    1265617 | LiteDataPublishedToReview | 2019-03-27 00:00:00.000 | 4550540631.00   |             2659077 | 2019-03-27 16:09:41.000 |
| Yellow  |    1265618 | LiteDataProcessed         | 2019-03-07 00:00:00.000 | 0.00            |             2659077 | 2019-03-07 16:53:16.000 |
| Yellow  |    1265619 | FullDataProcessed         | 2019-03-07 00:00:00.000 | 96139872.00     |             2659077 | 2019-03-07 16:53:16.000 |
| Yellow  |    1265620 | LiteDataProcessed         | 2019-03-08 00:00:00.000 | 105357273318.00 |             2659077 | 2019-03-08 16:43:24.000 |
| Yellow  |    1265621 | FullDataProcessed         | 2019-03-08 00:00:00.000 | 0.00            |             2659077 | 2019-03-08 16:43:24.000 |
+---------+------------+---------------------------+-------------------------+-----------------+---------------------+-------------------------+

这是我的尝试：

SELECT 
    CAST(YEAR(ps.DateCollected) AS VARCHAR(4)) + '-' + right('00' + CAST(MONTH(ps.DateCollected) AS VARCHAR(2)), 2),
    ps.[Name],
    c.name,
    ceiling(SUM(ps.FileSizeInBytes)/1024/1024/1024.0)  [Processed]
FROM EDDSDBO.RPCCProcessingStatistics ps
inner join edds.eddsdbo.[case] c on c.artifactid = ps.workspaceartifactid
where ps.DateCollected >= '2018-12-01'
GROUP BY ps.name, c.name,  CAST(YEAR(ps.DateCollected) AS VARCHAR(4)) + '-' + right('00' + CAST(MONTH(ps.DateCollected) AS VARCHAR(2)), 2)

逻辑应该是这样的：(1)获取2018-12-01之后的所有值(以字节为单位) (2)将它们相加(3)转换为GB (4)上限结果

当我运行我的代码并将FullDataProcessed的结果相加时，我得到了22。然而，当我手动将FullDataProcessed的结果相加时，我得到了15.40，当上限d时，它是16。

我期望代码结果中的FullDataProcessed等于16，而不是22。

Answer 1

我猜测您的一条或多条记录在edds.eddsdbo.case表中多次指定了它的would _ that。案例表上的主键是否不仅仅是artifactid？