2 ms·

Maybe I've not fully understood pivot tables (quite possible), but this example[0] seems to support my understanding... aren't pivot tables basically just aggr

Maybe I've not fully understood pivot tables (quite possible), but this example[0] seems to support my understanding... aren't pivot tables basically just aggregate functions such as `sum` and `avg` applied to a window of the table data (pivoted by rows -> columns)? Some of the executives I work with like to relate all their work in Excel and they just love pivot tables. I showed one of them the output of a table of data on web page backed by a database and they asked if I could export the data to Excel to make a pivot table that would then be displayed on the web page. Of course, I implemented their pivot table as queries against the database and created a new view to display it. I've never understood why I would need Excel to make Pivot tables when I already have SQL. [0] https://blogs.msdn.microsoft.com/spike/2009/03/03/pivot-tables-in-sql-server-a-simple-sample/ https://blogs.msdn.microsoft.com/spike/2009/03/03/pivot-tabl...

bshipp 7y ago
You're absolutely right. As someone coming into Python and SQL from a purely Excel background, the first realization I had was that I had been, in essence, programming Excel. Not in a rational VBA kind of way, but in how I was linking all of my cells (and sheets and books) and then using pivot tables, etc., to extend Excel beyond a single column/row perspective. Microsoft PowerPivot (or whatever it used to be called) was my gateway drug into the world of SQL. I can generate similar utilities to Excel/PivotTables using Plotly Dash or Pandas or even DataTables, but even after a few years of learning and practising it would take me less than 1/10th the time to generate an equivalent excel pivot and chart and have it displayed in front of a group. I'm not saying it's better, just that those execs are so comfortable with that visual-feedback excel approach that it'll be an uphill battle to convert them to a programmatic one. Pivot tables provide very rapid means of filtering, modifying output, and aggregating information than is otherwise possible inside Excel. To those coming from a two dimensional excel spreadsheet world, discovering pivot tables is like viewing the world in 3D. I don't see why you would go to Excel pivot tables from SQL; you already have a more powerful tool at your disposal, if you're comfortable with it. Going from Excel to SQL? That hurdle is a bit higher.
- iagovar 7y ago
  Well, because Excel is quick. I know R and SQL, and I still use Excel a lot. If I want a quick overview of a problem, query db from Excel -> PowerQuery -> PivotTables. It's easy to use, it's very easy to find solutions in google for PowerQuery, and it just feels slick. If I need in depth analysis then is when I switch to programming.
mongol 7y ago
If you know SQL well, you have a strong toolbox for sure. But pivot tables are strong too, especially in exploratory "slicing and dicing" of data. It is slightly faster for some purposes and that makes it more convenient. I tend to use SQL for the heavy lifting and then explore further with pivot tables.
PeterisP 7y ago
Pivot tables are quite useful for quick ad-hoc exploration of various different group-by structures with immediate feedback, empowering end-users who wouldn't be able to make the queries themselves, and waiting for someone else to do it would make it not worth it for exploration. If the data allows that, then a data export (usually requiring a bunch of aggregation and preprocessing) to a format suitable for excel pivot tables or some of the many business analytics tools often is quite useful to users who want to analyze the data. However, it should be expected that a side-effect of such ad-hoc data analysis often is specific reports that become understood to be useful, and which can then be re-implemented "properly" i.e. as a sql query/report that gets run in an automated, tested, reliable way and delivered where needed without that manual analysis step.

bshipp 7y ago

You're absolutely right. As someone coming into Python and SQL from a purely Excel background, the first realization I had was that I had been, in essence, programming Excel. Not in a rational VBA kind of way, but in how I was linking all of my cells (and sheets and books) and then using pivot tables, etc., to extend Excel beyond a single column/row perspective. Microsoft PowerPivot (or whatever it used to be called) was my gateway drug into the world of SQL. I can generate similar utilities to Excel/PivotTables using Plotly Dash or Pandas or even DataTables, but even after a few years of learning and practising it would take me less than 1/10th the time to generate an equivalent excel pivot and chart and have it displayed in front of a group. I'm not saying it's better, just that those execs are so comfortable with that visual-feedback excel approach that it'll be an uphill battle to convert them to a programmatic one. Pivot tables provide very rapid means of filtering, modifying output, and aggregating information than is otherwise possible inside Excel. To those coming from a two dimensional excel spreadsheet world, discovering pivot tables is like viewing the world in 3D. I don't see why you would go to Excel pivot tables from SQL; you already have a more powerful tool at your disposal, if you're comfortable with it. Going from Excel to SQL? That hurdle is a bit higher.

iagovar 7y ago
Well, because Excel is quick. I know R and SQL, and I still use Excel a lot. If I want a quick overview of a problem, query db from Excel -> PowerQuery -> PivotTables. It's easy to use, it's very easy to find solutions in google for PowerQuery, and it just feels slick. If I need in depth analysis then is when I switch to programming.

mongol 7y ago

If you know SQL well, you have a strong toolbox for sure. But pivot tables are strong too, especially in exploratory "slicing and dicing" of data. It is slightly faster for some purposes and that makes it more convenient. I tend to use SQL for the heavy lifting and then explore further with pivot tables.

PeterisP 7y ago

Pivot tables are quite useful for quick ad-hoc exploration of various different group-by structures with immediate feedback, empowering end-users who wouldn't be able to make the queries themselves, and waiting for someone else to do it would make it not worth it for exploration. If the data allows that, then a data export (usually requiring a bunch of aggregation and preprocessing) to a format suitable for excel pivot tables or some of the many business analytics tools often is quite useful to users who want to analyze the data. However, it should be expected that a side-effect of such ad-hoc data analysis often is specific reports that become understood to be useful, and which can then be re-implemented "properly" i.e. as a sql query/report that gets run in an automated, tested, reliable way and delivered where needed without that manual analysis step.