sql - 组内行之间的差异

Question

我正在将 R 代码翻译成 SQL。

我的 R 代码如下:

temp <- questions %>%
  select(UserId, ResultIndicator, ThemeId) %>%
  filter(UserId == 72) %>%
  group_by(ThemeId, ResultIndicator) %>%
  arrange(desc(ResultIndicator)) %>%
  summarise(Nominal = n()) %>% 
  mutate(Percent = Nominal/sum(Nominal)) %>%
  mutate(Percent = round(Percent, 3) * 100)  %>%  
  mutate(diff = Percent - lag(Percent, default = first(Percent)))

输出如下:

structure(list(ThemeId = c(11L, 11L, 12L, 12L, 13L, 19L), ResultIndicator = c("Correct", 
"Wrong", "Correct", "Wrong", "Correct", "Wrong"), Nominal = c(34L, 
4L, 25L, 2L, 10L, 1L), Percent = c(89.5, 10.5, 92.6, 7.4, 100, 
100), diff = c(0, -79, 0, -85.2, 0, 0)), class = c("grouped_df", 
"tbl_df", "tbl", "data.frame"), row.names = c(NA, -6L), vars = "ThemeId", labels = structure(list(
    ThemeId = c(11L, 12L, 13L, 19L)), class = "data.frame", row.names = c(NA, 
-4L), vars = "ThemeId", labels = structure(list(ThemeId = c(11L, 
12L, 13L, 19L, 22L, 33L, 35L, 38L, 48L, 56L, 59L, 62L, 71L, 77L
)), row.names = c(NA, -14L), class = "data.frame", vars = "ThemeId", drop = TRUE), indices = list(
    0:1, 2:3, 4L, 5L, 6:7, 8:9, 10:11, 12L, 13:14, 15:16, 17:18, 
    19:20, 21:22, 23:24), drop = TRUE, group_sizes = c(2L, 2L, 
1L, 1L, 2L, 2L, 2L, 1L, 2L, 2L, 2L, 2L, 2L, 2L), biggest_group_size = 2L), indices = list(
    0:1, 2:3, 4L, 5L), drop = TRUE, group_sizes = c(2L, 2L, 1L, 
1L), biggest_group_size = 2L)

对于非 R 用户，以上翻译为

  ThemeId ResultIndicator Nominal Percent  diff
1      11         Correct      34    89.5   0.0
2      11           Wrong       4    10.5 -79.0
3      12         Correct      25    92.6   0.0
4      12           Wrong       2     7.4 -85.2
5      13         Correct      10   100.0   0.0
6      19           Wrong       1   100.0   0.0

我在 SQL 中的尝试是这样的:

SELECT Count(Id) as Nominal, ResultIndicator, ThemeId
FROM LogUserQuestions
WHERE UserId = 72
GROUP BY ThemeId, ResultIndicator
ORDER BY ThemeId

但我不知道如何计算滞后。我尝试过:

(Nominal - lag(Nominal) over (partition by [not sure] order by [not sure])) as diff

但我不能使用 Nominal，因为它是后来创建的。

有什么提示吗？

Answer 1

我认为是这样的:

SELECT ResultIndicator, ThemeId, COUNT(*) as Nominal, 
       COUNT(*) * 1.0 / SUM(COUNT(*)) OVER (),
       COUNT(*) - LAG(COUNT(*)) OVER (ORDER BY ResultIndicator) as diff
FROM LogUserQuestions
WHERE UserId = 72
GROUP BY ThemeId, ResultIndicator
ORDER BY ResultIndicator DESC;