本文為英文版的機器翻譯版本，如內容有任何歧義或不一致之處，概以英文版為準。

# AWS Clean Rooms 差異隱私權的 SQL 功能
<a name="dp-sql-capabilities"></a>

AWS Clean Rooms 差異隱私權使用一般用途查詢結構來支援複雜的 SQL 查詢。根據此結構驗證自訂分析範本，以確保它們可以在受差異隱私權保護的資料表上執行。下表指出支援哪些 函數。如需詳細資訊，請參閱[查詢結構和語法](analysis-rules-custom.md#dp-query-structure-syntax)。


| Category | Spark 分析引擎支援的 SQL 建構 | 常見資料表表達式 (CTEs) | 最終 SELECT 子句 | 
| --- |--- |--- |--- |
| Aggregate functions |    ANY\_VALUE 函數   APPROXIMATE PERCENTILE\_DISC 函數   AVG 函數   COUNT 和 COUNT DISTINCT 函數   MAX 函數   MEDIAN 函數   MIN 函數   PERCENTILE\_CONT 函數   STDDEV\_SAMP 和 STDDEV\_POP 函數   SUM 和 SUM DISTINCT 函數   VAR\_SAMP 和 VAR\_POP 函數    | Supported with the condition that CTEs using differential privacy protected tables must result in data with user-level records. You should write the SELECT expression in those CTEs using `SELECT userIdentifierColumn...' format. | Supported aggregations: AVG, COUNT, COUNT DISTINCT, STDDEV, and SUM. | 
| CTEs | WITH clause, WITH clause subquery | Supported with the condition that CTEs using differential privacy protected tables must result in data with user-level records. You should write the SELECT expression in those CTEs using `SELECT userIdentifierColumn...' format. | N/A | 
| Subqueries |    SELECT   HAVING   JOIN   JOIN 條件   FROM   WHERE    | You can have any subquery that doesn't reference differential privacy relations in these constructs. You can have any subquery that references differential privacy relations in a FROM and JOIN clause only. | 
| Join clauses |    INNER JOIN   LEFT JOIN   左半聯結   左 ANTI 加入   RIGHT JOIN   完整加入   【JOIN】 OR 運算子   CROSS JOIN    | 支援的條件是，在使用者識別符資料欄上僅支援等同聯結的 JOIN 函數，並且在查詢開啟差異隱私權的兩個或多個資料表時是強制性的。確保強制性的同等加入條件是正確的。確認資料表擁有者已在所有資料表中設定相同的使用者識別符欄，以便使用者的定義在資料表之間保持一致。<br />在開啟差異隱私權的情況下結合兩個或多個關係時，不支援 CROSS JOIN 函數。 | 
| Set operators | UNION, UNION ALL, INTERSECT, EXCEPT \| MINUS (these are synonyms) | UNION, UNION ALL, INTERSECT, EXCEPT \| MINUS (these are synonyms) | Not supported | 
| Window functions | 彙總函數  AVG 範圍函數   COUNT 範圍函數   CUME\_DIST 範圍函數   DENSE\_RANK 範圍函數   FIRST\_VALUE 範圍函數   LAG 範圍函數   LAST\_VALUE 範圍函數   LEAD 範圍函數   MAX 視窗函數   MEDIAN 視窗函數   MIN 視窗函數   NTH\_VALUE 範圍函數   STDDEV\_SAMP 和 STDDEV\_POP 視窗函數 (STDDEV\_SAMP 和 STDDEV 是同義詞）   SUM 視窗函數   VAR\_SAMP 和 VAR\_POP 視窗函數 (VAR\_SAMP 和 VARIANCE 是同義詞）  <br />排名函數  DENSE\_RANK 範圍函數   NTILE 範圍函數   PERCENT\_RANK 範圍函數   RANK 範圍函數   ROW\_NUMBER 範圍函數   | All are supported with the condition that the user identifier column in the window function's partition clause is required when you query a relation with differential privacy turned on. | Not supported | 
| Conditional expressions |    CASE 條件表達式   COALESCE 表達式   GREATEST 和 LEAST 函數   NVL 和 COALESCE 函數   NVL2 函數   NULLIF 函數    | All are supported | All are supported | 
| Conditions |    比較條件   邏輯條件   模式比對條件   BETWEEN 範圍條件   Null 條件    | EXISTS and IN can't be used because they require subqueries. All others are supported. | All are supported | 
| Date-time functions |    交易中日期與時間函數   串連運算子   ADD\_MONTHS 函數   CONVERT\_TIMEZONE 函數   CURRENT\_DATE 函數   DATEADD 函數   DATEDIFF 函數   DATE\_PART 函數   DATE\_TRUNC 函數   EXTRACT 函數   TO\_TIMESTAMP 函數   日期或時間戳記函數的日期部分    | All are supported | All are supported | 
| String functions |    \|\| （串連） 運算子   BTRIM 函數   CHAR\_LENGTH 函數   CHARACTER\_LENGTH 函數   CONCAT 函數   LEFT 和 RIGHT 函數   LEN 函數   LENGTH 函數   LOWER 函數   LPAD 和 RPAD 函數   LTRIM 函數   POSITION 函數   REGEXP\_COUNT 函數   REGEXP\_INSTR 函數   REGEXP\_REPLACE 函數   REGEXP\_SUBSTR 函數   REPEAT 函數   REPLACE 函數   REVERSE 函數   RTRIM 函數   SPLIT\_PART 函數   SUBSTRING 函數   TRANSLATE 函數   TRIM 函數   UPPER 函數    | All are supported | All are supported | 
| Data type formatting functions |    CAST 函數   TO\_CHAR   TO\_DATE 陣列   TO\_NUMBER   日期時間格式字串   數值格式字串    | All are supported | All are supported | 
| Hash functions |    AES\_ENCRYPT   AES\_DECRYPT   ENCODE   DECODE   MD5 函數   SHA1 函數   SHA2 函數   XX\_HASH64    | All are supported | All are supported | 
| Mathematical operator symbols | \+, -, \*, /, %, and @ | All are supported | All are supported | 
| Math functions |    ABS 函數   ACOS 函數   ASIN 函數   ATAN 函數   ATAN2 函數   CBRT 函數   CEILING (或 CEIL) 函數   COS 函數   COT 函數   DEGREES 函數   LTRIM 函數   EXP 函數   FLOOR 函數   LN 函數   LOG 函數   MOD 函數   PI 函數   POWER 函數   RADIANS 函數   RANDOM 函數   ROUND 函數   SIGN 函數   SIN 函數   SQRT 函數   TRUNC 函數    | All are supported | All are supported | 
| VARBYTE functions |    UNHEX、   UNBASE64   HEX    HLL\_SKETCH\_AGG、    HLL\_SKETCH\_ESTIMATE   HLL\_UNION   HLL\_UNION\_AGG    | All are supported | All are supported | 
| JSON |    TO\_JSON   GET\_JSON\_OBJECT    | All are supported | All are supported | 
| Array functions |    ARRAY\_CONTAINS   ARRAY\_DISTINCT   ARRAY\_EXCEPT   ARRAY\_INTERSECT   ARRAY\_JOIN   ARRAY\_REMOVE   ARRAY\_SORT   ARRAY\_UNION    | Not supported | Not supported | 
| Extended GROUP BY | GROUPING SETS, ROLLUP, CUBE | Not supported | Not supported | 
| Sort operation | ORDER BY | Supported with the condition that an ORDER BY clause is only supported in a window function's partition clause when querying tables with differential privacy turned on. | Supported | 
| Row limits | LIMIT, OFFSET | Not supported in CTEs using differential privacy protected tables | All are supported | 
| Table and column aliasing |   | Supported | Supported | 
| Math functions on aggregate functions |   | Supported | Supported | 
| Scalar functions within aggregate functions |   | Supported | Supported | 

## 不支援 SQL 建構的常見替代方案
<a name="common-alternatives"></a>


| Category | SQL 建構 | 備用 | 
| --- |--- |--- |
| 範圍函數 |    LISTAGG   PERCENTILE\_CONT   PERCENTILE\_DISC    | You can use the equivalent aggregate function with GROUP BY. | 
| Mathematical operator symbols |    $column \|\|/ 2   $column \|/ 2   $column ^ 2    |    CBRT   SQRT   POWER($column， 2)    | 
| Scalar functions |    SYSDATE   $column：：integer   convert(type， $column)    |    CURRENT\_DATE   CAST $column AS 整數   CAST $column AS 類型    | 
| Literals | INTERVAL ‘1 SECOND' | INTERVAL '1' SECOND | 
| Row limiting | TOP n | LIMIT n | 
| Join |    USING   NATURAL    | ON clause should explicitly contain a join criterion. |