![]() However, using the above solution had virtually no increase on the query time (comparing with using simply the SUM), and should be completely reliable! It should be able to help others in a similar situation so I'm posting it here. Just wrap it into an outer query to sort to your needs. The CHECKSUM solution was also far too slow in its conversion, particularly as a result of the various data types, and I couldn't risk its unreliability. And due to the complexity, statistics simply wasn't a viable option. The table is very large, and using a sub-query dramatically increased the query time. My specific problem required me to divide a SUM by the COUNT of the distinct combination of various foreign keys and a date field, grouping by another foreign key and occasionally filtering by certain values or keys. SELECT COUNT(DISTINCT CAST(DocumentId as binary(4)) + CAST(DocumentSessionId as binary(4))) Assuming DocumentId and DocumentSessionId are both ints, and are therefore 4 bytes long. If you're working with datatypes of fixed length, you can cast to binary to do this very easily and very quickly. ![]() ![]() Obviously the chosen separator must be a character, or set of characters, which can never appear in either column. Given the following data the concatenating solution provided above will miscount: col1 col2 SQL> select count(distinct concat(deptno,job)) from emp I went down a blind alley with analytics but the answer was depressingly obvious. SQL> select distinct deptno, job from emp Pg supports two comparison statements IS DISTINCT FROMand IS NOT DISTINCT FROM, these essentially treat NULL as if it was a known value, rather than a special case for unknown. ![]() It certainly works as you might expect in Oracle. What is it about your existing query that you don't like? If you are concerned that DISTINCT across two columns does not return just the unique permutations why not try it? ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |