[dba-Tech] finding the most consistent combination of 3 columns

[Rocky Smolin]

Groovy.  I get to cook up a solution.  I don't have to do the work.  Life
is good!

What's the application?  Inquiring minds...

r

On Mon, Mar 8, 2021 at 6:36 PM John Bartow <jbartow at winhaven.net> wrote:

> After Stuart's suggestion turned out to prove that I was thinking wrongly,
> I wrote a function to apply your suggestion:
> Function Closeness(n1 As Double, n2 As Double, n3 As Double) As Double
> Dim ABval As Double, BCval As Double, ACval As Double
> ABval = Abs(n1 - n2)
> BCval = Abs(n2 - n3)
> ACval = Abs(n1 - n3)
> If (n1 + n2 + n3) > 0 Then 'had to add this because of rows with all zeros
> which are meaningless
>     Closeness = ABval + BCval + ACval
> Else
>     Closeness = 100
> End If
> End Function
>
> Results for test set sorted by preferable 'Distance':
> ROW     N1      N2      N3      Distance
> 35      6.00    6.00    6.00    0.00
> 29      0.00    0.00    0.20    0.40
> 27      0.50    0.50    1.00    1.00
> 18      2.00    1.50    1.50    1.00
> 37      5.00    6.00    5.00    2.00
> 22      2.00    1.00    1.00    2.00
> 38      5.00    5.00    6.00    2.00
> 39      6.00    5.00    5.00    2.00
> 40      6.00    7.00    6.00    2.00
> 36      5.00    6.00    6.00    2.00
> 26      1.00    0.00    1.20    2.40
> 19      2.50    1.00    1.50    3.00
> 24      1.00    0.20    2.00    3.60
> 25      1.00    0.10    2.00    3.80
> 21      2.50    0.50    2.50    4.00
> 17      3.50    1.50    1.50    4.00
> 41      6.00    7.00    5.00    4.00
> 10      5.00    2.00    5.00    6.00
> 15      4.00    3.00    0.00    8.00
> 28      1.00    1.00    5.00    8.00
> 16      5.00    1.00    1.00    8.00
> 42      5.00    7.00    9.00    8.00
> 20      1.50    0.75    5.00    8.50
> 12      6.00    1.00    2.00    10.00
> 23      0.00    5.00    0.00    10.00
> 13      6.00    1.00    1.00    10.00
> 9       7.00    3.00    1.00    12.00
> 43      5.00    9.00    11.00   12.00
> 7       6.00    6.00    0.00    12.00
> 11      7.00    1.00    2.00    12.00
> 3       6.00    8.00    0.00    16.00
> 4       8.00    6.00    0.00    16.00
> 8       0.00    11.00   0.00    22.00
> 6       12.00   0.00    0.00    24.00
> 5       12.00   0.00    0.00    24.00
> 44      3.00    9.00    15.00   24.00
> 2       4.00    12.00   0.00    24.00
> 1       4.00    20.00   0.00    40.00
> 14      0.00    0.00    22.00   44.00
> 31      0.00    0.00    0.00    100.00
> 32      0.00    0.00    0.00    100.00
> 33      0.00    0.00    0.00    100.00
> 34      0.00    0.00    0.00    100.00
> 30      0.00    0.00    0.00    100.00
>
> And so I have a beginning - thanks!
> John
> -----Original Message-----
> From: dba-Tech <dba-tech-bounces+jbartow=winhaven.net at databaseadvisors.com>
> On Behalf Of Rocky Smolin
> Sent: Monday, March 08, 2021 1:19 PM
> To: Discussion of Hardware and Software issues <
> dba-tech at databaseadvisors.com>
> Subject: Re: [dba-Tech] finding the most consistent combination of 3
> columns
>
> This may be a simplistic approach but...
>
> Suppose you take the absolute value between A and B and the absolute value
> between B and C and sum them.  Then the lowest value would be the three
> numbers that are the closest.
>
> You could also and the absolute value between A and C, add that to the
> first two absolute values.  Don't know if that's appropriate to your
> application, though.
>
> But using this approach you don't have to consider the magnitude of the
> numbers.  6 and 7 have the same 'distance' value as 600 and 700.
>
> Now - curious - what is the application?
>
> r
>
>