求R语言dplyr中对应SQL Server窗口函数的实现方法

Hey there! I get you're looking to replicate those SQL Server window functions—where you calculate COUNT, SUM, MIN, or MAX over specific partitions without reducing the number of returned rows—using dplyr in R. Let's break this down step by step.

First, let's recap your SQL example to make sure we're aligned:

SELECT
Account,
Item_Code,
Views,
COUNT(Views) OVER (PARTITION BY Account, Item_Code),
SUM(Views) OVER (PARTITION BY Account, Item_Code),
MIN(Views) OVER (PARTITION BY Account, Item_Code),
MAX(Views) OVER (PARTITION BY Account, Item_Code)
FROM your_table;

In dplyr, this translates perfectly using group_by() (to define the partition) and mutate() (to add the aggregated columns while keeping all rows). Here's how to do it:

Step 1: Load dplyr and create sample data #

First, let's set up a sample dataset to work with (matching the structure of your SQL table):

library(dplyr)

# Simulate your dataset (includes a NA value to show how missing data is handled)
df <- tibble(
  Account = c("A1", "A1", "A1", "A2", "A2"),
  Item_Code = c("I01", "I01", "I02", "I01", "I01"),
  Views = c(100, NA, 200, 150, 300)
)

Step 2: Implement the window functions #

Use group_by() to define your partition (matching PARTITION BY in SQL), then mutate() to calculate each statistic:

result <- df %>%
  group_by(Account, Item_Code) %>%
  mutate(
    # COUNT(Views) OVER (...) → counts non-NA values in Views
    count_views = sum(!is.na(Views)),
    # SUM(Views) OVER (...)
    sum_views = sum(Views, na.rm = TRUE),
    # MIN(Views) OVER (...)
    min_views = min(Views, na.rm = TRUE),
    # MAX(Views) OVER (...)
    max_views = max(Views, na.rm = TRUE),
    # Bonus: COUNT(*) OVER (...) → counts all rows in the partition (including NAs)
    count_total_rows = n()
  ) %>%
  ungroup()  # Optional: removes grouping if you don't need it for subsequent operations

# View the result
print(result)

Key Notes on the Mapping #

Let's clarify how each part matches your SQL code:

• group_by(Account, Item_Code) = PARTITION BY Account, Item_Code: This defines the groups (windows) we calculate statistics within.
• mutate(): Unlike summarise() (which collapses groups into single rows), mutate() adds new columns while preserving every row in the original dataset—exactly like SQL window functions.
• Handling missing values: We use na.rm = TRUE in sum(), min(), and max() to ignore NA values, just like SQL does automatically for aggregate functions.
• COUNT(Views) vs COUNT(*): In SQL, COUNT(Views) counts non-null values, while COUNT(*) counts all rows. In dplyr, these map to sum(!is.na(Views)) and n() respectively.

Example Output #

Running the code above will give you a dataframe like this:

# A tibble: 5 × 8
  Account Item_Code Views count_views sum_views min_views max_views count_total_rows
  <chr>   <chr>     <dbl>       <int>     <dbl>     <dbl>     <dbl>            <int>
1 A1      I01         100           1       100       100       100                2
2 A1      I01          NA           1       100       100       100                2
3 A1      I02         200           1       200       200       200                1
4 A2      I01         150           2       450       150       300                2
5 A2      I01         300           2       450       150       300                2

You can see that every original row is preserved, and the aggregated values are repeated across each partition group—just like your SQL query would return.

内容的提问来源于stack exchange，提问作者James