SAP HANA图形视图中按条件计算平均值的问题求助

Let's work through your problem to get those calculation columns right. First, let's recap the requirements clearly:

• Calculate the total average (of your target numeric column) divided by the number of distinct values in ColumnA
• Calculate the average of your target column where ColumnD = 'Ab', divided by that same distinct ColumnA count
• Calculate the average of your target column where ColumnD = 'Xy', divided by that same distinct ColumnA count

Step 1: Correctly Calculate the Distinct ColumnA Count (Counter) #

Your existing Counter column might be missing window function context, which causes it to return group-level counts instead of the global distinct count across the entire dataset. Replace its expression with:

COUNT(DISTINCT "ColumnA") OVER () AS "Counter"

The OVER () clause ensures this calculates the distinct count across all rows in your table, so every row will have the same correct value for the total unique ColumnA entries.

Step 2: Fix the Total Average Divided by Counter #

Assuming your target numeric column (the one you're averaging) is named ColumnVal, create this calculation column:

AVG("ColumnVal") OVER () / "Counter" AS "Total_Avg_Divided"

This first computes the global average of ColumnVal across all rows, then divides it by the precomputed Counter value.

Step 3: Fix the ColumnD='Ab' Average Divided by Counter #

Your CA_AVG_Ab was likely failing because it didn't use conditional aggregation with a global window. Replace its expression with:

AVG(CASE WHEN "ColumnD" = 'Ab' THEN "ColumnVal" ELSE NULL END) OVER () / "Counter" AS "CA_AVG_Ab"

The CASE statement filters rows to only include those where ColumnD = 'Ab', AVG(...) OVER () computes the global average of those filtered values, and then we divide by the Counter to get your desired result.

Step 4: Add the ColumnD='Xy' Average Divided by Counter #

Use the same conditional aggregation pattern for the 'Xy' case:

AVG(CASE WHEN "ColumnD" = 'Xy' THEN "ColumnVal" ELSE NULL END) OVER () / "Counter" AS "CA_AVG_Xy"

Why Your Previous Calculations Might Have Failed #

• Without OVER (), aggregate functions in HANA calculation columns operate on the current row's group (if any), not the entire dataset. This leads to incorrect counts/averages that are per-group instead of global.
• Missing the conditional CASE statement for filtering ColumnD values would result in including all rows, not just the 'Ab'/'Xy' subset you need.

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