Selecting based on rank
You can select values based on certain criteria, for example if their rank puts them in a certain range.
Difficulty
Objective
You want to know which ten stores sold the most of a specific product during a given time
period. You know how to do this in SQL by using commands and functions such as
SUM
, WHERE
, and GROUP BY
, and you would
like to know how to obtain the same results with 1010data.
SQL solution
To find the ten stores that had the highest sales of item 406444 during 2015, you would execute the following query.
SELECT store,sku,ROUND(SUM(qty)) AS `Sum of Sales` FROM sales_item_detail WHERE sku=406444 AND YEAR(date)=2015 GROUP BY store ORDER BY SUM(qty) DESC LIMIT 10;
Using SELECT
you can select the store number, SKU, and the sum of sales
from the Sales Item Detail table. Additionally, you can select only
the rows that contain the specified SKU and that occurred during the given time period.
Grouping by the store and ordering by the sum of sales, you can obtain a list of the stores
and their sales, ranked by the highest sum of sales. Using LIMIT
, only the
top ten stores are shown in the result.
With 1010data, these same results can be produced and additionally the results can be easily saved to a table or used in future queries. To use these results for additional analysis in SQL, you would have to nest this select statement within another query.
1010data Macro Language solution
<base table="pub.doc.retail.altseg.sales_detail_transid"/> <sel value="year(trans_date)=2015&sku=406444"/> <tabu label="Sum of Product Sales" breaks="store,sku"> <tcol source="xsales" fun="sum" name="sum_xsales" label="Sum of`Extended`Sales"/> </tabu> <sel value="g_rank(;;;sum_xsales)<=10"/> <sort col="sum_xsales" dir="down"/>
This analogue finds the ten stores that had the highest sales of the product with SKU 406444 during the year 2015. Therefore, you first use a selection statement to obtain only the transactions made in 2015 for SKU 4016444. A tabulation is then done to obtain the sum of sales for each store.
After the sum for each store is calculated, you can use the
g_rank(G;S;O;X)
to select the top ten stores with the highest sales. The
<sort>
operation is then used to mimic the results produced by SQL and
order the stores by highest to lowest sales.
Alternate 1010data Macro Language solution
<base table="pub.doc.retail.altseg.sales_detail_transid"/> <sel value="year(trans_date)=2015&sku=406444"/> <tabu label="Sum of Product Sales" breaks="store,sku"> <tcol source="xsales" fun="sum" label="Sum of`Extended`Sales"/> </tabu> <sort col="t0" dir="down"/> <sel value="i_<11"/>
Alternatively, you can sort the Sum of Extended Sales
column in
descending order after the tabulation, and then use a selection statement to manually select
the top ten stores.
Further reading
If you would like to learn more about the functions and operations discussed in this recipe, click on the links below: