PostgreSQL Window Functions（PostgreSQL窗口功能）.pdfVIP

PostgreSQL Window Functions 2009 Window functions in PostgreSQL 8.4 Contents Introduction 1 Simple common cases 2 Running totals 2 Gap analysis2 Motivation table2 Derivative and Integral 4 Derivative 4 Integral 6 Stock control with FIFO costing 6 Conclusion 12 Introduction Very often we need access to previous/next rows in row set to calculate particular values for columns. The classical case is running totals. If we have two columns Number and Amount we may want third column Total where in first row we have value of Amount in second – Amount(1) + Amount(2). In third – Amount(1) + Amount(2) + Amount(3), etc. To summarize data we use GROUP BY clause, but the main trouble is that each row of row set may take part only in one group and therefore in one summary. In that paradigm we need to multiply rows to get a separate copy of particular row for each summary. For running total we need n*n/2 rows for n rows in result. But a great feature windowing was introduced by SQL:2003. Windowing allows us to do 2 major things Aggregate calculation over rows of the query results. GROUP BY needs separate subset of rows to create each summary value. Windowing allow us to create summary values on any subset of query results without need to query the subset separately for each summary value. In other words we calculate summaries during querying the result rows and mix summaries and single values within one row. Access particular previous and next rows while calculating values of current row. But since the SQL:2003 standard was announced we had no support for windowing in PostgreSQL. At last, in version 8.4 we get the feature ready to use. In this article I show you the power and ease of window functions in the context of practical tasks. I wont describe windowing. There are man