What is the best approach to sort resultant columns
Lets say we have product catalogue which is having 60k products , now we have to show these products in the dashboard based on Units sold,Orders,Revenue,Customers,Add-to-carts,Cart abandonments,Views, ,Clicks,Click through rate,Cart through rate,Conversion rate,Inventory left,Average daily sales,Sales per view, Sales per click etc.
All these columns are derived from multiple tables after joining and by default it is sorted based revenue column.
Every table is having the projections created based on columns present on the table ,not on these derived columns.
Now from the dashboard user might performs sorting in any of these derived column . What is the best approach to sort the records.
Tagged:
0
Answers
Check out the doc page Choosing Sort Order: Best Practices for some guidence.
You can also pass the query for the Dashboard into Database Designer to have it generate optimized projections for you.
Sounds like it might be a good use-case for Flattened Tables.
https://www.vertica.com/docs/11.0.x/HTML/Content/Authoring/AnalyzingData/FlattenedTables/FlattenedTables.htm?zoom_highlight=flattened tables
If you define your master table as a flattened table, you can define all those references to your lookups in that definition. It doesn't change the way you load your master table, and then when you query it, you can query the references from the other tables as if they are columns in the master, without having to code the join in your SQL. So, it simplifies your SQL drastically.