More Fun with Analytics

Moderator: NorbertKrupa

Post Reply
User avatar
JimKnicely
Site Admin
Site Admin
Posts: 1825
Joined: Sat Jan 21, 2012 4:58 am
Contact:

More Fun with Analytics

Post by JimKnicely » Fri Aug 10, 2012 12:53 pm

Analytic functions in Vertica can handle complex analysis and reporting tasks.

For instance, from the data below I can find out the sum, average, minimum and maximum pet food cost overall and by pet type.

Code: Select all

dbadmin=> select * from pets;
  pet_type  |  pet_name  | food_cost
------------+------------+-----------
cat        | Garfield   |        50
guniea pig | Fudge      |        25
cat        | Oscar Mowl |       100
dog        | Fido       |       325
dog        | Spot       |       400
cat        | Heathcliff |        75
dog        | Sam        |       100
rabbit     | Oreo       |       115
(8 rows)

Code: Select all

dbadmin=> select pet_type,
dbadmin->        pet_name,
dbadmin->        food_cost,
dbadmin->        sum(food_cost) over () sum_overall,
dbadmin->        sum(food_cost) over (partition by pet_type) sum_pet_type,
dbadmin->        avg(food_cost) over () avg_overall,
dbadmin->        avg(food_cost) over (partition by pet_type) avg_pet_type
dbadmin->   from pets
dbadmin-> order
dbadmin->     by pet_type,
dbadmin->        pet_name;
  pet_type  |  pet_name  | food_cost | sum_overall | sum_pet_type | avg_overall | avg_pet_type
------------+------------+-----------+-------------+--------------+-------------+--------------
cat        | Garfield   |        50 |        1190 |          225 |      148.75 |           75
cat        | Heathcliff |        75 |        1190 |          225 |      148.75 |           75
cat        | Oscar Mowl |       100 |        1190 |          225 |      148.75 |           75
guniea pig | Fudge      |        25 |        1190 |           25 |      148.75 |           25
dog        | Fido       |       325 |        1190 |          825 |      148.75 |          275
dog        | Sam        |       100 |        1190 |          825 |      148.75 |          275
dog        | Spot       |       400 |        1190 |          825 |      148.75 |          275
rabbit     | Oreo       |       115 |        1190 |          115 |      148.75 |          115
(8 rows)

Code: Select all

dbadmin=> select pet_type,
dbadmin->        pet_name,
dbadmin->        food_cost,
dbadmin->        min(food_cost) over () min_overall,
dbadmin->        min(food_cost) over (partition by pet_type) min_pet_type,
dbadmin->        max(food_cost) over () max_overall,
dbadmin->        max(food_cost) over (partition by pet_type) max_pet_type
dbadmin->   from pets
dbadmin-> order
dbadmin->     by pet_type,
dbadmin->        pet_name;
  pet_type  |  pet_name  | food_cost | min_overall | min_pet_type | max_overall | max_pet_type
------------+------------+-----------+-------------+--------------+-------------+--------------
cat        | Garfield   |        50 |          25 |           50 |         400 |          100
cat        | Heathcliff |        75 |          25 |           50 |         400 |          100
cat        | Oscar Mowl |       100 |          25 |           50 |         400 |          100
guniea pig | Fudge      |        25 |          25 |           25 |         400 |           25
dog        | Fido       |       325 |          25 |          100 |         400 |          400
dog        | Sam        |       100 |          25 |          100 |         400 |          400
dog        | Spot       |       400 |          25 |          100 |         400 |          400
rabbit     | Oreo       |       115 |          25 |          115 |         400 |          115
(8 rows)
Have fun!
Jim Knicely

Image

Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.

Post Reply

Return to “Vertica Tips, Lessons and Examples”