It's so frustrating - the more I'm learning from these dailies, the more I'm recognizing that my current knowledge is basically very limited ... 🙂 Great information ... keep going ! Greetings from Germany.
Thanks.. Great Explanation. I used this feature to calculate the most dominant pattern of purchase from within a group/basket of products (a,b,c - 3 products) which are identified via a Rule/Pattern found via the Association Rules ML model. The ML Model works w/o consideration of sequence or order of purchase. Using this we can improve the status quo - overall we have 10 transactions where customers have purchased (a,b,c) but the most likely sequence of purchase (dominant pattern, in sequence of purchase) is b,a,c with this occurring 6 times out of 10 overall.
in the sense that you could do something like (using the demo from the video) case when rank() over ( partition by city order by pop desc) = 1 then city_name end but you'ld still end up with inline views etc
Are you sure the output is correct? If the data is correct the city name of Mumbai should be what should show up in the line for India. Wondering if it is a data problem or a query problem.
In the table I got this - but I make no claim on up-to-dateness/accuracy of the data SQL> select city, population from cities 2 where country = 'India' 3 order by population desc; CITY POPULATION -------------- ---------- Delhi 28514000 Mumbai 19980000 Kolkata 14681000 Bangalore 11440000 Chennai 10456000 Hyderabad 9482000 Ahmedabad 7681000 Surat 6564000 Pune 6276000 9 rows selected.
Pretty much any aggregation. You could even use MIN to mess with people :-) But yes, I like the ANY_VALUE option because its more self-documenting in that sense
I believe, on the off-chance that multiple cities have the same population (say, we use default =100 and not actual values) within a country, the aggregation fn breaks the tie-break and gives the max city (alphabetically ordered). Any _Value would also break the tie but result would be non-deterministic.
@@cosmos177 If I understand correctly, ties can be broken by adding CITY DESC to the ORDER BY, making it deterministic: ANY_VALUE(CITY) KEEP (DENSE_RANK FIRST ORDER BY POPULATION DESC, CITY DESC)
Thank you, Connor. Very similar video is here th-cam.com/video/Y1O7LTjhPPM/w-d-xo.html. My question is does the KEEP clause work to output the names of the cities by country with the highest population, assuming there may be more than one city in a country with the same highest population? If that is not possible, I may lean towards using our regular way of doing as it works in that case as well.
It's so frustrating - the more I'm learning from these dailies, the more I'm recognizing that my current knowledge is basically very limited ... 🙂
Great information ... keep going !
Greetings from Germany.
Every day we all get an extra 1% on the Oracle features.... it all adds up
best background of all programming channels for sure
thankyou!
😂😂😂
Wow. This going to get used lot in my SQLs from today.
Thanks.. Great Explanation.
I used this feature to calculate the most dominant pattern of purchase from within a group/basket of products (a,b,c - 3 products) which are identified via a Rule/Pattern found via the Association Rules ML model. The ML Model works w/o consideration of sequence or order of purchase. Using this we can improve the status quo - overall we have 10 transactions where customers have purchased (a,b,c) but the most likely sequence of purchase (dominant pattern, in sequence of purchase) is b,a,c with this occurring 6 times out of 10 overall.
glad it was useful
Well done Connor. Your explanations are so simple too. Thanks a lot
Glad you like them!
Awesome!
Thanks, love these quick videos and you explain so well.
You're very welcome!
Oh this is a keeper.. Thanks C
glad its useful
This information is so useful. You are great! Thank you
Glad it was helpful!
Very useful indeed, 1 question though, how is it different from FIRST_VALUE analytical function?
They are similar but notice KEEP works in an aggregation sense (ie, with a GROUP BY)
@@DatabaseDude Awesome :)
Great stuff, thank you!
Glad you enjoyed it!
Does this work the same way as RANK OVER (Partition ...) ?
in the sense that you could do something like (using the demo from the video)
case when rank() over ( partition by city order by pop desc) = 1 then city_name end
but you'ld still end up with inline views etc
Are you sure the output is correct? If the data is correct the city name of Mumbai should be what should show up in the line for India. Wondering if it is a data problem or a query problem.
In the table I got this - but I make no claim on up-to-dateness/accuracy of the data
SQL> select city, population from cities
2 where country = 'India'
3 order by population desc;
CITY POPULATION
-------------- ----------
Delhi 28514000
Mumbai 19980000
Kolkata 14681000
Bangalore 11440000
Chennai 10456000
Hyderabad 9482000
Ahmedabad 7681000
Surat 6564000
Pune 6276000
9 rows selected.
Nice
Is it necessary to use MAX()? Or can we use ANYVALUE()?
Pretty much any aggregation. You could even use MIN to mess with people :-) But yes, I like the ANY_VALUE option because its more self-documenting in that sense
I believe, on the off-chance that multiple cities have the same population (say, we use default =100 and not actual values) within a country, the aggregation fn breaks the tie-break and gives the max city (alphabetically ordered). Any _Value would also break the tie but result would be non-deterministic.
@@cosmos177 If I understand correctly, ties can be broken by adding CITY DESC to the ORDER BY, making it deterministic:
ANY_VALUE(CITY) KEEP (DENSE_RANK FIRST ORDER BY POPULATION DESC, CITY DESC)
Thank you, Connor. Very similar video is here th-cam.com/video/Y1O7LTjhPPM/w-d-xo.html. My question is does the KEEP clause work to output the names of the cities by country with the highest population, assuming there may be more than one city in a country with the same highest population? If that is not possible, I may lean towards using our regular way of doing as it works in that case as well.
Unfortunately LISTAGG is not supported as a KEEP function currently
This is great but if more than cites have the same population, only the MAX(city) is listed, not all the cities.
This is true, but I'd content that this is often the requirement as well