技术难题：无需自连接实现实体值的权重分配

Sample Data #

Rows are uniquely identified by {entity_id, category_id, group_id}, with consistent weights for each category/group across all rows:

entity_id entity_value category_id category_weight group_id group_weight
1         100          11          6               101      4
1         100          11          6               102      3
1         100          12          5               102      3
1         100          12          5               103      2
1         100          13          6               101      4

Key Observations #

• Entities can pair any categories with any groups (no inherent link between categories and groups)
• Data is redundant but consistent: same category_id or group_id has identical weights across all rows

Goal #

Distribute the entity's value to each row in two stages:

1. First split the value across categories using their weights
2. Then split each category's allocated value across its associated groups using group weights

Step 1: Category-level Allocation #

Entity 1 links to categories {11,12,13} with weights {6,5,6}

Category 11: 100*(6/(6+5+6)) ≈ 35.29
Category 12: 100*(5/(6+5+6)) ≈ 29.41
Category 13: 100*(6/(6+5+6)) ≈ 35.29

Step 2: Group-level Allocation (Per Category) #

Entity 1 - Category 11 links to groups {101,102} with weights {4,3}

Group 101: 35.29*(4/(4+3)) ≈ 20.17
Group 102: 35.29*(3/(4+3)) ≈ 15.12

Entity 1 - Category 12 links to groups {102,103} with weights {3,2}

Group 102: 29.41*(3/(3+2)) ≈ 17.65
Group 103: 29.41*(2/(3+2)) ≈ 11.76

Entity 1 - Category 13 links to group {101} with weight {4}

Group 101: 35.29*(4/4) = 35.29

Existing Implementation (With Self-Join) #

Your current query uses a self-join to calculate total category weights per entity:

SELECT 
  sample.entity_id, 
  sample.category_id, 
  sample.group_id, 
  sample.entity_value AS original_value, 
  sample.entity_value * 
    (sample.category_weight / entity.total_category_weight) * 
    (sample.group_weight / SUM(sample.group_weight) OVER (PARTITION BY sample.entity_id, sample.category_id)) AS apportioned_value 
FROM ( 
  SELECT 
    entity_id, 
    SUM(category_weight) AS total_category_weight 
  FROM ( 
    SELECT 
      entity_id, 
      category_id, 
      MAX(category_weight) AS category_weight 
    FROM sample 
    GROUP BY entity_id, category_id 
  ) entity_category 
  GROUP BY entity_id 
) entity 
INNER JOIN sample ON sample.entity_id = entity.entity_id

Concise Alternative (No Self-Joins) #

Absolutely! You can eliminate the self-join entirely by leveraging window functions to compute the total unique category weights per entity directly. Here are a few approaches depending on your SQL dialect:

Approach 1: Using DISTINCT in Window Functions (Modern Dialects) #

Most modern databases (PostgreSQL, MySQL 8+, SQL Server, etc.) support DISTINCT within window functions. This lets us sum the unique category weights per entity in one step:

SELECT
  entity_id,
  category_id,
  group_id,
  entity_value AS original_value,
  entity_value *
    (category_weight / SUM(DISTINCT category_weight) OVER (PARTITION BY entity_id)) *
    (group_weight / SUM(group_weight) OVER (PARTITION BY entity_id, category_id)) AS apportioned_value
FROM sample;

• SUM(DISTINCT category_weight) OVER (PARTITION BY entity_id) calculates the total of unique category weights for each entity (6+5+6=17 for entity 1)
• The rest of the logic matches your original query's group-level allocation

Approach 2: Compatible with Older Dialects #

If your database doesn't support DISTINCT in window functions, use a subquery to get the unique category weight per entity-category pair first:

SELECT
  entity_id,
  category_id,
  group_id,
  entity_value AS original_value,
  entity_value *
    (category_weight / SUM(unique_cat_weight) OVER (PARTITION BY entity_id)) *
    (group_weight / SUM(group_weight) OVER (PARTITION BY entity_id, category_id)) AS apportioned_value
FROM (
  SELECT
    *,
    -- Get the consistent weight for each entity-category pair
    FIRST_VALUE(category_weight) OVER (PARTITION BY entity_id, category_id) AS unique_cat_weight
  FROM sample
) subquery;

• FIRST_VALUE grabs the weight for each entity-category (since it's consistent across rows)
• We then sum these unique weights per entity to get the total category weight

Approach 3: Single Query with Grouping #

Another option is to use grouping to ensure we only count each category's weight once per entity:

SELECT
  entity_id,
  category_id,
  group_id,
  entity_value AS original_value,
  entity_value *
    (category_weight / SUM(MAX(category_weight)) OVER (PARTITION BY entity_id)) *
    (group_weight / SUM(group_weight) OVER (PARTITION BY entity_id, category_id)) AS apportioned_value
FROM sample
GROUP BY entity_id, category_id, group_id, entity_value, category_weight, group_weight;

• MAX(category_weight) returns the consistent weight for each entity-category
• SUM(MAX(category_weight)) OVER (PARTITION BY entity_id) sums these weights to get the total category weight for the entity

All these approaches produce the same result as your original query but eliminate the need for self-joins, making the code cleaner and potentially more efficient.

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