Defining unique sets of records in a dataset

• bart.hermans

  SSC Rookie

  Points: 29

  More actions

  December 17, 2015 at 4:12 pm

  #100598

  I work for a logistics company.

  Customers send packages from point A to B and pay a certain amount for that.

  Tariff data is stored in the following way:

  AccountNumbervarchar(9)

  Productvarchar(1)

  Originvarchar(3)

  Destinationvarchar(3)

  Pointervarchar(3)

  Some sample data:

  AccountNumberProductOriginDestinationPointer

  1234567891ABCDEFXXX

  1234567891GHIJKLYYY

  1234567891MNOPQRYYY

  1234567892ABCDEFZZZ

  1234567892GHIJKLZZZ

  1234567893ABCDEFYYY

  1234567893GHIJKLYYY

  There are hundreds of thousands of accounts.

  Each account can have up to 36 different products (A-Z, 0-9) set up.

  There are thousands of possible Origins and Destinations.

  A combination of AccountNumber, Product, Origin and Destination points to a Pointer, which in turn defines the tariff a customer pays (not important for my question).

  A zone is defined as a set of orgin-destination combinations under a specific AccountNumber, Product and Pointer.

  These are the zones from above sample data:

  AccountNumberProductPointerZoneOrigin-Destination

  1234567891XXX1ABC-DEF

  YYY2GHI-JKL

  MNO-PQR

  2ZZZ3ABC-DEF

  GHI-JKL

  3YYY4ABC-DEF

  GHI-JKL

  As you can see, Zones 3 and 4 are equal (same set of origin-destination combinations).

  What we’re trying to do, is create a list of distinct zones.

  In other words, we’re trying to identify unique sets of records.

  What we’ve tried so far:

• checksum_agg(expression)

  this aggregate function accepts an integer expression and calculates a checksum over a set of records. This is actually exactly what we were looking for, but the problem with this function is that there’s a very high possibility of collisions. We converted the varchar origin and destination values to an int value and fed that to the aggregate and noticed ourselves that the collision rate was extremely high, which made the aggregate useless to us.

• What we did then was use some math. Again, we converted the origin and destinations to integers and calculated the min, max, sum, avg, stdev for these sets of records and used the result to identify unique sets. This works fine, but is really slow.
• The last thing we tried, was build a custom aggregate function in c#. This custom aggregate works as follows:

  select AccountNumber, Product, Pointer, AggregateChecksum(hashbytes(‘md5’, Origin + ‘|’ + Destination))

  This works pretty well and is the fastest solution we found up until now. We’re using it in production and we’re satisfied with the results, but we’re still looking for a faster way to identify these zone sets.

So this is my question: does anyone know of a faster/better way to identify unique sets of records?