NoCOUG SQL Challenge Author Solution, TSQL Challenge #12 !
NoCOUG Challenge
After many months, Iggy Fernandez published his own solution of the first annual NoCOUG SQL Challenge.
His solution was performing fast from the beginning according to the graph he sent to Chen Shapira back in the announcement days :
I never managed to achieve those kind of results, only Alberto and André could throw that magnitude of dices, I really bow at them.
Looking at how he proceed, it's as he said quite close to the André Aurajo's solution, with some more tricks.
Instead of building in advance the self cross joined probabilities, he build the query at the needed level using bitand, concatenation and string aggregation, and then he executes it with DBMS_XMLGET.GETXMLTYPE and formating it with XMLTable.
I didn't know this trick of executing result via the GETXMLTYPE function, I'll try to remember that.
Be sure to check his whole solution because it is very interesting.
TSQL Challenge #12
The TC team published the statement of their TSQL Challenge #12.
This time it is a row generator problem.
From this table :
----------- -----------
200903 100
200803 95
200802 99
200801 100
200711 100
You have to make slide the score for every months up to another entry in the table or up to today :
----------- -----------
200908 100
200907 100
200906 100
200905 100
200904 100
200903 100
200902 95
200901 95
200812 95
200811 95
200810 95
200809 95
200808 95
200807 95
200806 95
200805 95
200804 95
200803 95
200802 99
200801 100
200712 100
200711 100
For TSQL Challenge #11 I couldn't find any valuable Oracle solution over what I published using SQL Server.
I won't publish my SQL Server solution before the closure date is past, but I'm publishing my Oracle solution now, because it uses two things SQL Server doesn't have :
- an easy row generator trick
- some valuable analytic funtions
I'm sure it won't help anyone on finding the TSQL solution.
I've inserted the datas into a table named TC12.
First, I want to build one row for every month between the first month of the table and now.
I'm using the connect by level trick to achieve this.
The query may look heavy but it's mostly due to data conversion :
AS
(
SELECT
to_number(to_char(add_months(min_ym, level-1), 'yyyymm')) AS yearmonth
FROM
( SELECT to_date(to_char(min(yearmonth)), 'yyyymm') AS min_ym
FROM TC12 )
CONNECT BY
level-1 <= months_between(trunc(sysdate, 'mm'), min_ym)
)
SELECT * FROM AllMonths;
YEARMONTH
----------------------
200711
200712
200801
200802
200803
200804
200805
200806
200807
200808
200809
200810
200811
200812
200901
200902
200903
200904
200905
200906
200907
200908
From here, I just have to outer join with the table :
AM.yearmonth AS "YearMonth",
TC.score AS "Score"
FROM
AllMonths AM
LEFT OUTER JOIN TC12 TC
ON AM.yearmonth = TC.yearmonth
ORDER BY
AM.yearmonth DESC;
YearMonth Score
---------------------- ----------------------
200908
200907
200906
200905
200904
200903 100
200902
200901
200812
200811
200810
200809
200808
200807
200806
200805
200804
200803 95
200802 99
200801 100
200712
200711 100
It's nice but all the new value are nulls.
Oracle offer the last_value analytic functions which does this job :
AS
(
SELECT
to_number(to_char(add_months(min_ym, level-1), 'yyyymm')) AS yearmonth
FROM
( SELECT to_date(to_char(min(yearmonth)), 'yyyymm') AS min_ym
FROM TC12 )
CONNECT BY
level-1 <= months_between(trunc(sysdate, 'mm'), min_ym)
)
SELECT
AM.yearmonth AS "YearMonth",
last_value(TC.score IGNORE nulls) over(ORDER BY AM.yearmonth ASC) AS "Score"
FROM
AllMonths AM
LEFT OUTER JOIN TC12 TC
ON AM.yearmonth = TC.yearmonth
ORDER BY
AM.yearmonth DESC;
YearMonth Score
---------------------- ----------------------
200908 100
200907 100
200906 100
200905 100
200904 100
200903 100
200902 95
200901 95
200812 95
200811 95
200810 95
200809 95
200808 95
200807 95
200806 95
200805 95
200804 95
200803 95
200802 99
200801 100
200712 100
200711 100
And voilà for the Oracle solution, 20 lines of code !
I also found two SQL Server solutions but taking almost 40 lines of code I'll use the remaining time to see how I may improve them.
This entry is filed under SQLing. And tagged with analytic functions, Challenges, Connect By, NoCOUG, Oracle, SQL Server, TSQL. You can follow any responses to this entry through RSS 2.0. You can leave a response, or trackback from your own site.
[...] instruction about integers using arithmetics function. He already did this for date functions in the comments of this topic. I hope he will sum it up in a clean and well explained post that I could refer to [...]
- To have the query return any result, substitude "<" with < (copy / paste accident)
Two tricks :
- As i generated the months between two bounds, the resulting interval was : [0, 11] instead of [1, 12]
... where the value 0 should have been 12.
How can you fix this using only "arithmetic" operators ?
After trying various combinations of interval translations, i was just looking for the "discontinous" expression that would return 1 when 0 and 0 when between 1 and 11.
My first thought was that "floor" is the simplest "discontinous" function.
1 <= x 0 < (12 - x) / 12 floor((12 - x) / 12) = 0
x = 0 => (12 - x) / 12 = 1
Then, we just have to use this boolean "belonging" function to add 12 to the base expression only when x = 0 :
x + 12 * floor((12 - x) / 12)
(instead of x)
- In the real world, we use date functions to perform operations between two dates, because the year and the months are not in the same radix.
But of course, when you substract yyyymm1 to yyyymm, there should be a simple arithmetic expression that transforms the result into a year / month expression. That's what all the mod(, 88) stuff is :
mm - mm1 belongs to [1, 12] when mm > mm1, [99, 88] when mm1 < mm
(mod 88 lets the first interval unchanged, translates the second one into [1, 11])
[...] couldn't use (nor understand) the arithmetics Pacmann used in the comments of the previous blog [...]
Hehe, I didn't notice my mistake... until i was posting it (and comparing to your results). I should have corrected every interval... But instead i just hoped noone would see the cheat :)
Thanks for linking my Pacblog !
Of course i've linked yours too (first of all i had to find out how the administration interface works :)). There are now too sites : your's and the Master's !
Hi Pacmann ! Glad to see you around here, I hope you noticed that I've added the pacblog to the links.
You did a small cheat in your solution adding the current month into the initial rowset !
It's always nice to see other ways on how to solve the same problem !
Hi Waldar !
It's always a pleasure to visit your blog.
Until now, i just enjoyed your posts, and was each time really impressed to see you were always learning / finding new tricks :)
But today, i was really motivated by this #12 challenge and tried to write a little variation of your query.
I tried it in the opposite way : prepare the data with lead function, then theta-join it with the number CTE...
The rest are just practical jokes (maybe because i was bored at the office :)) :
- using arithmetic difference and mod manipulation to avoid the date functions
- arithmetic tricks to change a 0 into a 12 without CASE
- row generation with dba_objects...
Hum, i'm just looking forward to see your T-SQL implementation !
Good luck ;)
SELECT to_number(to_char(sysdate, 'yyyymm')) AS ym, 100 AS sc FROM DUAL UNION ALL
SELECT 200903 AS ym, 100 AS sc FROM DUAL UNION ALL
SELECT 200803 , 95 FROM DUAL UNION ALL
SELECT 200802 , 99 FROM DUAL UNION ALL
SELECT 200801 , 100 FROM DUAL UNION ALL
SELECT 200711 , 100 FROM DUAL
)
, u AS(
SELECT t.ym, lead(ym, 1) over(ORDER BY ym DESC) ymprv, lead(sc, 1) over (ORDER BY ym DESC) AS sc, row_number() over(ORDER BY ym DESC) rk
FROM t)
, v AS (
SELECT u.ym - ymprv AS diffbrut, ym, ymprv, sc, trunc((u.ym - ymprv)/100) AS diffy, mod(mod(u.ym - ymprv, 100), 88) AS diffm
FROM u
)
, w AS (
SELECT row_number() over(ORDER BY NULL) nb
FROM dba_objects)
, x AS (
SELECT v.sc,v.ym - trunc(w.nb / 12) * 100 - mod(w.nb, 12) AS res, ym, nb
FROM v JOIN w ON w.nb <= v.diffy * 12 + v.diffm
)
SELECT trunc(res / 100) * 100 + mod(mod(res, 100), 88) - 88 * trunc( (12 - mod(mod(res, 100), 88)) / 12) AS res, sc
FROM x
union ALL
SELECT ym, sc
FROM u
WHERE rk = 1
ORDER BY res DESC
RES SC
--------- ----------
200908 100
200907 100
200906 100
200905 100
200904 100
200903 100
200902 95
200901 95
200812 95
200811 95
200810 95
200809 95
200808 95
200807 95
200806 95
200805 95
200804 95
200803 95
200802 99
200801 100
200712 100
200711 100