作者
digoal
日期
2021-12-02
标签
PostgreSQL , 高级SQL
https://baike.baidu.com/item/%E5%9B%BE%E5%AE%BE%E6%A0%B9%E5%A4%A7%E5%AD%A6/2568273
艾伯哈特-卡尔斯-图宾根大学（拉丁文：Universitas Eberhardina Carolina；德文：Eberhard-Karls-Universitaet Tuebingen）简称图宾根大学，坐落于原符腾堡伯国故都、今巴登-符腾堡州大学城图宾根，由艾伯哈特伯爵于公元1477年创建，是欧洲最古老的大学之一，德国精英大学、德国U15大学联盟、欧洲研究型大学协会、欧洲公民大学联盟、欧洲大学协会、昴宿星大学联盟成员。
图宾根大学是一所世界百强大学，培养了9位诺贝尔奖得主以及哲学家黑格尔、诗人荷尔德林、天文学家开普勒、作家黑塞、德国前总统霍斯特·克勒、前总理库尔特·乔治·基辛格、中国外交官乔冠华等杰出校友，在社会科学、自然科学以及医学等领域取得的成就蜚声世界。
图宾根大学位列2022QS世界大学排名第177位 [15] ；2021泰晤士高等教育世界大学排名第78位；2020世界大学学术排名第150-200区间。
https://db.inf.uni-tuebingen.de/teaching/AdvancedSQLSS2017.html
Relational database systems provide efficient storage for large volumes of data. This course highlights that these systems also provide a versatile and expressive data processing language: SQL. There's much more to SQL than the plain SELECT...FROM...WHERE clause and we will see that a surprisingly large number of algorithmic problems can be tackled using SQL. Moving computation close to the data is key to unlock the true potential of database systems.
Selected course topics include
- common table expressions (WITH),
- non-standard data types (arrays, geometric data, JSON, XML),
- table functions,
- window functions,
- recursive computation,
- user-defined SQL procedures (PL/SQL),
- index design for complex SQL queries,
- off-beat SQL applications, useful SQL idioms, and fun SQL puzzles.
The course will only provide a brief introduction to the fundamental aspects of relational database systems. We expect you to have basic SQL skills (through prior attendance of Datenbanksysteme I or personal projects, for example) or be willing to acquire such skills.
Join us for a boatload of SQL fun! We will provide more course details once the semester approaches.
- 0 Handout
- Instructions for participating in the "Advanced SQL" exercises.
- 1 Welcome
- Administrativa
- Fundamentals of the tabular (relational) data model
- 2 The Core of SQL
- A tour of core SQL constructs
- Query conventions in this course
- 3 Standard and Non-Standard Data Types
- Type casts (in particular from type text)
- The variety of types of values that may be stored in table cells:
- text and numeric data
- ranges
- user-defined enumerated types
- dates, times, timestamps, and intervals
- bit strings
- binary large objects (BLOBs)
- geometric objects
- JSON and XML documents
- sequences
- 4 Arrays and User-Defined Functions
- The type
τ[](orτ array) - Computation over arrays
- Array unnesting and aggregation
- Table-generating functions
- User-defined SQL functions (UDFs)
LATERAL(sideways row variable passing)- Sample problem (Finding Seats)
- The type
- 5 Window Functions
- Window Frames
ROWSandRANGEframesWINDOWclause- Partitioning
- Sample problem (Weekend Weather)
- Sample problem (Visibility in the Hills)
- Scans
- Window Functions
- Sessionization
- Run-Length Encoding
- Landscape Features
- Numbering and Ranking Rows
- Consecutive Ranges
- Piecewise Linear Approximations
- 6 Recursion
- Expressive power of SQL and recursion
WITH RECURSIVE- Self-referential queries
- Set vs. bag semantics (
UNIONvs.UNION ALL) - Home-made
generate_series() - Tree traversals
- (Non-)termination
- Connected components in a graph
- Recursive text processing (regular expression matching)
- Bag semantics and termination
- Recursive array processing (Sudoku)
- Loose index scans
- K-Means clustering
- Marching squares (control flow to data flow)
- Cellular automata (Game of Life, Liquid flow)
- Parsing context-free grammars (CYK algorithm)
- 7 Procedural SQL
- PL/SQL = Scripting + SQL
- Saving turnarounds
- Blocks, Statements, Expressions
- Invoking Queries, populating tables
- Implementation of a spreadsheet core (JSON-based formula representation, dependency extraction, topological sorting, recursive formula evaluation)
https://www.youtube.com/playlist?list=PL1XF9qjV8kH12PTd1WfsKeUQU6e83ldfc
期望 PostgreSQL 增加什么功能?
类似Oracle RAC架构的PostgreSQL已开源: 阿里云PolarDB for PostgreSQL云原生分布式开源数据库!
PostgreSQL 解决方案集合
德哥 / digoal's github - 公益是一辈子的事.
