postgresql에 sql-server에 있는 rollup(), cube(), grouping() grouping sets(), pivot절, unpivot절이 있는지 없는지 확인하고 기능 비교하고 없으면 대체할 수 있는 방법

PostgreSQL은 SQL Server와 유사한 그룹화 및 피벗 기능을 대부분 지원하지만, 구문과 사용법에 약간의 차이가 있습니다. 각 기능별로 비교해보겠습니다:

## ROLLUP

PostgreSQL은 ROLLUP을 지원합니다. 사용법은 SQL Server와 거의 동일합니다.

```sql
SELECT column1, column2, SUM(column3)
FROM table
GROUP BY ROLLUP (column1, column2);
```

## CUBE

PostgreSQL은 CUBE도 지원합니다. 사용법은 SQL Server와 유사합니다.

```sql
SELECT column1, column2, SUM(column3)
FROM table
GROUP BY CUBE (column1, column2);
```

## GROUPING SETS

PostgreSQL은 GROUPING SETS를 지원합니다. 사용법은 SQL Server와 동일합니다.

```sql
SELECT column1, column2, SUM(column3)
FROM table
GROUP BY GROUPING SETS ((column1, column2), (column1), (column2), ());
```

## GROUPING()

PostgreSQL은 GROUPING() 함수를 지원합니다. 사용법은 SQL Server와 동일합니다.

```sql
SELECT 
  column1, 
  column2, 
  SUM(column3),
  GROUPING(column1) AS grp_column1,
  GROUPING(column2) AS grp_column2
FROM table
GROUP BY ROLLUP (column1, column2);
```

## PIVOT

PostgreSQL은 SQL Server의 PIVOT 절과 동일한 기능을 제공하지 않습니다. 하지만 비슷한 결과를 얻기 위해 다음과 같은 방법을 사용할 수 있습니다:

1. CASE 문과 집계 함수 사용
2. crosstab 함수 사용 (tablefunc 확장 필요)

예시 (CASE 문 사용):

```sql
SELECT 
  column1,
  SUM(CASE WHEN column2 = 'value1' THEN column3 ELSE 0 END) AS value1,
  SUM(CASE WHEN column2 = 'value2' THEN column3 ELSE 0 END) AS value2
FROM table
GROUP BY column1;
```

## UNPIVOT

PostgreSQL은 SQL Server의 UNPIVOT 절과 동일한 기능을 제공하지 않습니다. 하지만 UNION ALL을 사용하여 비슷한 결과를 얻을 수 있습니다.

예시:

```sql
SELECT column1, 'value1' AS column2, value1 AS column3 FROM table
UNION ALL
SELECT column1, 'value2' AS column2, value2 AS column3 FROM table
UNION ALL
SELECT column1, 'value3' AS column2, value3 AS column3 FROM table;
```

결론적으로, PostgreSQL은 ROLLUP, CUBE, GROUPING SETS, GROUPING() 함수를 SQL Server와 거의 동일하게 지원합니다. 그러나 PIVOT과 UNPIVOT은 직접적으로 지원하지 않으며, 대체 방법을 사용해야 합니다. PIVOT의 경우 CASE 문이나 crosstab 함수를, UNPIVOT의 경우 UNION ALL을 사용하여 유사한 결과를 얻을 수 있습니다[1][3][6][15].

출처
[1] MSSQL] PIVOT, UNPIVOT, GROUPING SETS, ROLLUP, CUBE https://bigexecution.tistory.com/221
[2] PostgreSQL equivalent for SQL Server GROUP BY WITH ROLLUP https://stackoverflow.com/questions/13990821/postgresql-equivalent-for-sql-server-group-by-with-rollup
[3] PostgreSQL Tutorial 06. GROUPING SETS, CUBE, ROLLUP - velog https://velog.io/@jwkim_1018/Section-6.-Grouping-sets-Cube-and-Rollup
[4] When to use GROUPING SETS, CUBE and ROLLUP - Stack Overflow https://stackoverflow.com/questions/25274879/when-to-use-grouping-sets-cube-and-rollup
[5] Understanding the differences between CUBE and ROLLUP https://stackoverflow.com/questions/7053471/understanding-the-differences-between-cube-and-rollup
[6] Pivot and Unpivot in SQL - GeeksforGeeks https://www.geeksforgeeks.org/pivot-and-unpivot-in-sql/
[7] Equivalent to unpivot() in PostgreSQL - Stack Overflow https://stackoverflow.com/questions/1128737/equivalent-to-unpivot-in-postgresql
[8] How to Use Grouping Sets, Cube, and Rollup in PostgreSQL - EDB https://www.enterprisedb.com/postgres-tutorials/how-use-grouping-sets-cube-and-rollup-postgresql
[9] SQL PIVOT and GROUPING SETS for Advanced Reporting https://www.mssqltips.com/sqlservertip/7167/sql-pivot-grouping-sets-advanced-reporting/
[10] [PDF] SQl Server to Aurora PostgreSQL Migration Playbook - awsstatic.com https://d1.awsstatic.com/whitepapers/Migration/sql-server-database-amazon-aurora-postgresql-migration-playbook-12.4.pdf
[11] Unpivot with Postgres - Thoughts about SQL https://blog.sql-workbench.eu/post/unpivot-with-postgres/
[12] Unpivoting data in PostgreSQL - Postgres OnLine Journal http://postgresonline.com/article_pfriendly/283.html
[13] Rollup in SQL Server - GeeksforGeeks https://www.geeksforgeeks.org/rollup-in-sql-server/
[14] SQL 데이터 피벗/언피벗 (pivot/unpivot ) - Dream Lovers - 티스토리 https://yunleee.tistory.com/35
[15] How to calculate Subtotals in SQL Queries https://www.sqlshack.com/how-to-calculate-subtotals-in-sql-queries/
[16] GROUP BY 절 - Amazon Redshift https://docs.aws.amazon.com/ko_kr/redshift/latest/dg/r_GROUP_BY_clause.html
[17] SETS, CUBE, ROLLUP의 개념, 효과적인 사용법 및 주의사항 https://junhkang.tistory.com/84
[18] 집계 확장 - Amazon Redshift https://docs.aws.amazon.com/ko_kr/redshift/latest/dg/r_GROUP_BY_aggregation-extensions.html
[19] ROLLUP, CUBE, GROUPING SETS 를 알아보자! - velog https://velog.io/@damool/rollup-cube-grouping-sets-%EB%A5%BC-%EC%95%8C%EC%9E%90%EB%B3%B4%EC%9E%90
[20] SQL 집계함수 - ROLLUP, CUBE, GROUPING SETS https://for-my-wealthy-life.tistory.com/44
[21] [SQL] ROLLUP(), CUBE(), GROUPING() 그리고 PIVOT(), UNPIVOT() https://jungyijun.tistory.com/103
[22] SQL Features and Tuning - jOOQ blog https://blog.jooq.org/sql/
[23] Equivalent to unpivot() in PostgreSQL - Stack Overflow https://stackoverflow.com/questions/1128737/equivalent-to-unpivot-in-postgresql
[24] SQL PIVOT and SQL UNPIVOT Examples to Transform Data https://www.mssqltips.com/sqlservertip/7233/sql-pivot-sql-unpivot-examples-transform-data/
[25] ROLLUP and CUBE | SQL Tutorial Documentation on data.world https://docs.data.world/documentation/sql/concepts/advanced/ROLLUP_and_CUBE.html
[26] Pivot and unpivot for T-SQL - SQL Server to Aurora PostgreSQL ... https://docs.aws.amazon.com/zh_cn/dms/latest/sql-server-to-aurora-postgresql-migration-playbook/chap-sql-server-aurora-pg.tsql.pivot.html
[27] GROUP BY for ANSI SQL - SQL Server to Aurora PostgreSQL ... https://docs.aws.amazon.com/dms/latest/sql-server-to-aurora-postgresql-migration-playbook/chap-sql-server-aurora-pg.sql.groupby.html
[28] Pivot in SQL Server || Pivoting and Grouping Sets in SQL - YouTube https://www.youtube.com/watch?v=V53C2r6qsKI
[29] PIVOT and UNPIVOT: Optimization and Execution Strategies in an ... https://www.researchgate.net/publication/221309644_PIVOT_and_UNPIVOT_Optimization_and_Execution_Strategies_in_an_RDBMS
[30] How to Use SQL PIVOT - DataCamp https://www.datacamp.com/tutorial/sql-pivot
[31] I had never heard of GROUP BY CUBE either! It looks like it's part of ... https://news.ycombinator.com/item?id=32365313
[32] Difference Between Cube and Rollup in SQL Server - GeeksforGeeks https://www.geeksforgeeks.org/difference-between-cube-and-rollup-in-sql-server/
[33] [오라클 데이터베이스] - 7. 다중행 함수와 데이터 그룹화(그룹화 함수 ... http://dailusia.blog.fc2.com/blog-entry-166.html
[34] GROUPING SETS feature in Polars · Issue #7948 · pola-rs ... - GitHub https://github.com/pola-rs/polars/issues/7948
[35] MaxCompute Unleashed - Part 7: Grouping Set, Cube and Rollup https://www.alibabacloud.com/blog/maxcompute-unleashed---part-7-grouping-set-cube-and-rollup_600808
[36] How to Migrate Oracle's UNPIVOT to PostgreSQL - EDB https://www.enterprisedb.com/postgres-tutorials/how-migrate-oracles-unpivot-postgresql
[37] sql: add support for GROUPING SETS, CUBE, and ROLLUP · Issue ... https://github.com/cockroachdb/cockroach/issues/46280
[38] PostgreSQL Tutorial 06. GROUPING SETS, CUBE, ROLLUP - velog https://velog.io/@jwkim_1018/Section-6.-Grouping-sets-Cube-and-Rollup
[39] PostgreSQL:How to use GROUPING SETS, CUBE, and ROLLUP for ... https://stackoverflow.com/questions/38428139/postgresqlhow-to-use-grouping-sets-cube-and-rollup-for-summary-totals
[40] Re: Unpivot / uncrosstab support? - PostgreSQL https://www.postgresql.org/message-id/C237184E7081314392F31AE826947FFA44E3A56E11@EXWA-MBX01.nexus.csiro.au
[41] GROUP BY절, HAVING절, 기타 그룹화 관련 함수 https://earth-95.tistory.com/156
[42] Query syntax | BigQuery - Google Cloud https://cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax
[43] MSSQL] PIVOT, UNPIVOT, GROUPING SETS, ROLLUP, CUBE https://bigexecution.tistory.com/221
[44] PIVOT and UNPIVOT examples - Amazon Redshift https://docs.aws.amazon.com/redshift/latest/dg/r_FROM_clause-pivot-unpivot-examples.html
[45] How to Use Grouping Sets, Cube, and Rollup in PostgreSQL - EDB https://www.enterprisedb.com/postgres-tutorials/how-use-grouping-sets-cube-and-rollup-postgresql
[46] SQL GROUPING SETS operator - Waitingforcode https://www.waitingforcode.com/sql/sql-grouping-sets-operator/read