USQL中如何实现多列组合的去重计数？

Since U-SQL doesn’t support the direct COUNT(DISTINCT col1, col2) syntax or subqueries in some contexts, let’s cover two robust alternatives—including a more efficient approach than dual alias queries:

1. Use a CTE to Group First, Then Count #

This is the most reliable and performant method, as it avoids string manipulation pitfalls. U-SQL fully supports Common Table Expressions (CTEs), which let you isolate the distinct pairs before counting:

WITH DistinctColPairs AS (
    SELECT col1, col2
    FROM YourTableName
    GROUP BY col1, col2 -- Groups unique (col1, col2) pairs
)
SELECT COUNT(*) AS DistinctPairCount
FROM DistinctColPairs;

This works because the CTE first generates only unique combinations of col1 and col2, then the outer query simply counts how many unique groups exist. No string parsing or ambiguity here—perfect for any data types.

2. Concatenate Columns with a Safe Separator #

If you prefer a one-liner, you can combine col1 and col2 into a single unique string (using a separator that won’t appear in your column values) and count distinct instances of that string:

SELECT 
    COUNT(DISTINCT CONCAT(col1.ToString(), '|', col2.ToString())) AS DistinctPairCount
FROM YourTableName;

⚠️ Important: Always use a separator that’s not present in either column’s data. For example, if col1 could contain pipes (|), pick a different character like ^ or ;; to avoid merging distinct pairs into identical strings.

Why These Are Better Than Dual Alias Queries #

Dual alias approaches often involve redundant processing or complex joins. The CTE method is cleaner, easier to maintain, and leverages U-SQL’s grouping optimizations. The concatenation method is concise but requires extra care to avoid data collisions.

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