Hi Roger, That was geeky! ;-) If you're looking for more challenges, how would you simulate GREATEST() and LEAST()? Or RPAD() and LPAD() in SQLite?
And the killer is: How to simulate START WITH ... CONNECT BY ... using CTE (including all functions and pseudo-columns) But very interesting! Your first solution looks just like that in java.lang.Long.bitCount(). I guess that would really be the optimal implementation. If unsigned shifting was available. But optimal is tricky to define in this case, as some databases do not implement shifting or bit_and... So I agree with you that somewhat "readable" might be OK in this case. Cheers and thanks for your insight! Lukas 2013/6/24 Roger Thomas <[email protected]> > I've spent some time looking at other options and have now come up with > the following > > SELECT mod ( > ( > (((results.myValue - ((results.myValue >> 1) & > 3952873730080618203) - ((results.myValue >> 2) & 1317624576693539401)) + > ((results.myValue - ((results.myValue >> 1) & > 3952873730080618203) - ((results.myValue >> 2) & 1317624576693539401) >> > 3)) & 8198552921648689607) > + > ((((results.myValue -((results.myValue >> 1) & > 3952873730080618203) - ((results.myValue >> 2) & 1317624576693539401)) + > ((results.myValue - ((results.myValue >> 1) & > 3952873730080618203) - ((results.myValue >> 2) & 1317624576693539401) >> > 3)) & 8198552921648689607) > ) >> 6 > ) > ) & 8088187786164957247 > ) > ,4095) AS Result > FROM > ( > SELECT 1256789 AS myValue > ) AS results > > > With some testing this seems to correctly count bits for 64bit long ints > on at least MYSQL (currently my only test target). > > Its a version of a 1970's solution known as HAKMEM 169, which was > orginally coded for the PDP10. Full background can be found on the web and > there is some nice detail here > > > http://blogs.msdn.com/b/jeuge/archive/2005/06/08/hakmem-bit-count.aspx > > A short background is that it works by suming up the number of bits in > each block of 3 bits, it then combines the results of each set of 2 blocks > and then finally uses MOD to create a sum of all the results. All of the > long 'magic' numbers make far more sense if viewed as octal values as they > are used as bitwise masks. The reason why the code is so small is because > each operation is working on a total of 22 blocks (21 x 3 bit and 1 x 1 > bit). Its not used nowadays as the MOD function is costly compaired to > other coding options. > > For general ease of use I would say that the solution already included in > jOOQ is good enough and far easier for people to understand if they look at > the queries. Compaired to my original post, this solution should be better > across platforms as it only reduces the 64 bit value so there is no > problems with maths overflows. > > > Roger > > -- > You received this message because you are subscribed to the Google Groups > "jOOQ User Group" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to [email protected]. > For more options, visit https://groups.google.com/groups/opt_out. > > > -- You received this message because you are subscribed to the Google Groups "jOOQ User Group" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. For more options, visit https://groups.google.com/groups/opt_out.
