3 ms·
>Which is itself the poor man's groupby. SQL GROUP BY creates summarized horizontal rows. Instead, pivot tables are more analogous to crosstab queries which c
by jasode 3y ago
>Which is itself the poor man's groupby.
SQL GROUP BY creates summarized horizontal rows.
Instead, pivot tables are more analogous to crosstab queries which creates summarized vertical columns. It "pivots" data groupings by rotating from horizontal to vertical.
The older versions of SQL dialects that didn't have the newer cross tab syntax required convoluted CASE syntax to "simulate" pivot tables which didn't really work that well since one had to know ahead of time -- all the unique values -- to put in each CASE condition branch.
- qsort 3y agoIf you are grouping over them, they shouldn't be columns in the first place, the original SQL is "right". Rows and columns aren't symmetric! Reshaping data should be a presentation-time decision, not a query-time decision. You have a dataset, a relation in the algebraic sense, and you are choosing to display it in some way: as a table, as a pivoted table, as a pie-chart... Conflating the two is a consequence of spreadsheets having an unbeatable UX but a terrible data model that lets you treat rows as columns and vice-versa.
- fifilura 3y agoThere are real use cases for pivoting data in SQL not only for presentation. Tables can be too "long". You need data points to be on the same row if you want to do arithmetic operations on on different types of values. For example k + 3.14* number_of_chimneys - 0.13 * age - 1.34*neigbourhood_criminality as house_price
- toyg 3y ago> Reshaping data should be a presentation-time decision When you potentially billions or trillions of data points, it isn't. Rows and columns have limits. You need some hard logic for true multidimensional data at scale.
- quantified 3y agoFor business analyst purposes, the reshape should be a display-time activity. Finance models may start with billions but tend to present a manageable quantity to the user. The chain of calculations back to any stored data is the real key, along with what your interactivity needs for multi-user update are.
- tomnipotent 3y ago> should be a presentation-time decision, not a query-time decision Agree, but sometimes you just need to shove the results of a SQL query into an Excel file and you don't want to get fancy. You're either 1) overwriting Sheet B and then using a pivot table in Sheet A to get the final presentation, 2) pivoting in the code/program executing the query before writing to Excel or 3) pivoting in SQL and skipping the code and Excel pivot table altogether. I run into this a lot with data used for financial modeling, or financial reporting that heavily relies on using dates/categories as column/row headers.
- mr_toad 3y agoCross tabulations are a fundamental part of statistics, business and scientific analysis. That they aren’t supported by the relational model just means that an RDBMS is not a full fledged analytical tool.
- quantified 3y agoSee my comment above on multidimensional axes for presentation vs primary key components of a relation. More interestingly, SQL has a hard time with the contextual calculations (actuals come from this table and are aggregated, forecasts come from those 12 tables and are computed by the following formula series, now apply that logic across N variables for actual and forecast) and a really painful time defining computed rows (like fields in some rows being ratios of the same field in other rows). That's what the modeling tools like Anaplan, Tidemark and some others excel at.
- quantified 3y agoYou're falling into the trap of rows and columns for visual layout vs tuples and fields for semantic layout. The axes of a multidimensional spreadsheet (there can be any number) are better thought of as primary key components and the cells of the sheet as non-key attribute values.