Pareto Percentage Calculation Fails Due to Integer Division in Window Functions

Window Function Calculations Yield Incorrect Percentage Results

Issue Overview: Misleading Percentage Values in Cumulative Sum Window Queries

When attempting to calculate cumulative percentages for Pareto analysis using SQLite window functions, users may encounter unexpected results where all percentage values except the last row display as 0.0%. This occurs despite correct cumulative sums (SizeSum) and total sums (Total) being calculated. The root cause lies in SQLite’s type handling during arithmetic operations. When dividing two integers before applying a multiplication factor (e.g., 100.0 for percentage conversion), SQLite performs integer division, truncating fractional results. This leads to near-zero percentages until the final row, where cumulative sum equals total sum (100%). The problem is exacerbated when window functions aggregate integer columns without explicit type casting, causing silent data truncation. The confusion arises because the individual components of the calculation (SizeSum and Total) appear valid, but their ratio fails to produce meaningful percentages until reordered or cast appropriately.

Possible Causes: Implicit Type Conversion and Operator Precedence Conflicts

1. Integer Division Truncation: SQLite uses dynamic typing, which means the result of division between two integers defaults to integer division. For example, 521 / 2697 becomes 0 instead of 0.1931, and multiplying by 100.0 afterward yields 0.0.

2. Window Function Evaluation Order: The order of operations within window function expressions matters. If division precedes multiplication, the intermediate result is an integer, whereas reversing the operations forces floating-point arithmetic.

3. Column Type Inference: If the Size column in the source table is stored as an integer, all window aggregates derived from it (e.g., SUM(Size)) will also be integers unless explicitly cast.

4. Query Materialization of CTEs: Using MATERIALIZED in Common Table Expressions (CTEs) can lock column types early, preventing runtime type promotion during later calculations.

5. Implicit vs. Explicit Casting: Relying on SQLite’s automatic type conversion may lead to inconsistencies, especially when intermediate results are materialized in temporary structures (e.g., CTEs or subqueries).

Troubleshooting Steps, Solutions & Fixes: Ensuring Floating-Point Arithmetic in Aggregates

Step 1: Diagnose Column Types and Intermediate Results

Verify the data type of the Size column in the dotFolder_ table using PRAGMA table_info(dotFolder_);. If Size is an integer, any sum over it will default to integer arithmetic. Execute intermediate calculations separately to observe truncation:

SELECT 521 / 2697;  -- Returns 0 (integer division)
SELECT 521 * 100.0 / 2697;  -- Returns 19.3177604746014 (float division)

Step 2: Force Floating-Point Division by Reordering Operations

Modify the percentage calculation to perform multiplication before division. This ensures the numerator is a float before division occurs:

SELECT 
  SUM(Size) OVER(...) * 100.0 / SUM(Size) OVER(...) AS Percentage

In the original query, the faulty expression was:

(SUM(Size) OVER(...) / SUM(Size) OVER(...)) * 100.0

Reversing the order of operations avoids integer division.

Step 3: Explicitly Cast Integer Columns to Float

If reordering operations is insufficient (e.g., when using complex CTEs), explicitly cast the Size column to a float early in the query:

WITH ordered AS (
  SELECT md5(Entry), CAST(Size AS REAL) AS Size 
  FROM dotFolder_ 
  WHERE Size > 0 
  ORDER BY Size DESC
)

Step 4: Avoid Premature Materialization of Integer Aggregates

Remove MATERIALIZED from CTE definitions unless necessary. Materialization can lock column types before calculations:

-- Replace:
WITH ordered(entry, Size) AS MATERIALIZED (...)
-- With:
WITH ordered(entry, Size) AS (...)

Step 5: Validate Window Frame Boundaries

Ensure window functions are correctly scoped. The original query uses:

SUM(Size) OVER(ORDER BY Size DESC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)

This is correct for cumulative sums. Verify that the ORDER BY clause within the window function matches the CTE’s ordering to prevent misaligned calculations.

Step 6: Compare with a Verified Working Query

The user’s working recursive CTE approach avoids integer division by using explicit floating-point operations in the pareto calculation:

SUM(CS.fsum) * 100.0 / O.ftot

Emulate this pattern in window functions by prioritizing multiplication before division.

Step 7: Analyze Query Plans for Implicit Type Handling

Use EXPLAIN QUERY PLAN to identify stages where temporary tables or B-tree sorts might enforce integer types. For example, the original query’s plan includes USE TEMP B-TREE FOR ORDER BY, which may persist integer types. Rewrite the query to minimize intermediate materialization.

Step 8: Benchmark Alternative Approaches for Efficiency

While the recursive CTE works, it may be less efficient than window functions. Compare execution times using .timer ON in SQLite. For large datasets, window functions with proper type handling usually outperform recursive queries.

Final Solution Code:

WITH ordered AS (
  SELECT md5(Entry) AS entry, Size
  FROM dotFolder_
  WHERE Size > 0
  ORDER BY Size DESC
  LIMIT 20 OFFSET 81
)
SELECT 
  entry,
  Size,
  SUM(Size) OVER (ORDER BY Size DESC ROWS UNBOUNDED PRECEDING) AS SizeSum,
  SUM(Size) OVER () AS Total,
  (SUM(Size) OVER (ORDER BY Size DESC ROWS UNBOUNDED PRECEDING) * 100.0) / 
  SUM(Size) OVER () AS Percentage
FROM ordered;

Key Takeaways:

1. Type Precedence in SQLite: Always ensure at least one operand in division is a float to avoid integer truncation.
2. Window Function Ordering: Align ORDER BY clauses in CTEs and window functions to maintain consistent sorting.
3. Minimize Materialization: Avoid unnecessary CTE materialization that might lock column types prematurely.

By adhering to these principles, users can leverage SQLite’s window functions effectively for Pareto analysis without falling prey to type conversion pitfalls.