runtimecompare approaches one problem · two ways
Compare Approaches
The same problem solved different ways in Python, run on the same input, with what really differs and when to use each.
pivot_table vs SQL conditional aggregation
A table of order counts: channels as rows, statuses as columns.
what differs
pivot_table discovers the status values and makes a column for each. SQL has no general pivot in SQLite, so each column is a SUM over a CASE; you must know the values.
at a glance
| approach | use it when |
|---|---|
| A · pandas pivot_table | Exploring data where the column values aren't known in advance. |
| B · SQL SUM(CASE …) | Reports in SQL: each output column is written out explicitly. |
Watch out: A status with no orders in a channel is 0 in both thanks to fill_value=0 and ELSE 0.
the outputs
Each approach runs on the same input.
concepts
pivotconditional aggregation
learn it
related comparisons