Thread: Sort question - Fractions, Metric etc
I have a table with metric, imperial, fraction columns. Is there a way to sort correctly using imperial (eg; .125, .375, .437 -> 1., 1.125) Couldn't handle it with ORDER BY ASC, DESC args so I added a sort_column and sorted based on those values eg; 1,2,3,4,5,6 indicating the value I need to show in which sequence. Just curious what the pro's do Bret
On 8/14/21 9:14 AM, Bret Stern wrote: > I have a table with metric, imperial, fraction columns. > > Is there a way to sort correctly using imperial (eg; .125, .375, .437 -> > 1., 1.125) Alright how is this different from metric or fraction? I can sort of see fraction if you mean as 1/3, 1/20, etc. What is the data type of the field you are storing this in? > > Couldn't handle it with ORDER BY ASC, DESC args so I added a sort_column > and sorted based > > on those values eg; 1,2,3,4,5,6 indicating the value I need to show in > which sequence. > > > Just curious what the pro's do > > Bret > > > -- Adrian Klaver adrian.klaver@aklaver.com
Strings; I haven't explored doing this with numeric types, but some columns needed alpha chars eg 13mm. Although I could have front ended this UI with mm nomenclature I did not. I'll put a table together with appropriate numeric types and see if the sort will behave. On 8/14/2021 9:21 AM, Adrian Klaver wrote: > On 8/14/21 9:14 AM, Bret Stern wrote: >> I have a table with metric, imperial, fraction columns. >> >> Is there a way to sort correctly using imperial (eg; .125, .375, .437 >> -> 1., 1.125) > > Alright how is this different from metric or fraction? > > I can sort of see fraction if you mean as 1/3, 1/20, etc. > > What is the data type of the field you are storing this in? > >> >> Couldn't handle it with ORDER BY ASC, DESC args so I added a >> sort_column and sorted based >> >> on those values eg; 1,2,3,4,5,6 indicating the value I need to show >> in which sequence. >> >> >> Just curious what the pro's do >> >> Bret >> >> >> > >
On 8/14/21 9:37 AM, Bret Stern wrote: > Strings; > > I haven't explored doing this with numeric types, but some columns > needed alpha chars eg 13mm. Two columns: data_val(numeric) data_unit(varchar) 13 mm > > Although I could have front ended this UI with mm nomenclature I did not. > > I'll put a table together with appropriate numeric types and see if the > sort will behave. It will. > > > > On 8/14/2021 9:21 AM, Adrian Klaver wrote: >> On 8/14/21 9:14 AM, Bret Stern wrote: >>> I have a table with metric, imperial, fraction columns. >>> >>> Is there a way to sort correctly using imperial (eg; .125, .375, .437 >>> -> 1., 1.125) >> >> Alright how is this different from metric or fraction? >> >> I can sort of see fraction if you mean as 1/3, 1/20, etc. >> >> What is the data type of the field you are storing this in? >> >>> >>> Couldn't handle it with ORDER BY ASC, DESC args so I added a >>> sort_column and sorted based >>> >>> on those values eg; 1,2,3,4,5,6 indicating the value I need to show >>> in which sequence. >>> >>> >>> Just curious what the pro's do >>> >>> Bret >>> >>> >>> >> >> -- Adrian Klaver adrian.klaver@aklaver.com
I like that idea On 8/14/2021 9:46 AM, Adrian Klaver wrote: > On 8/14/21 9:37 AM, Bret Stern wrote: >> Strings; >> >> I haven't explored doing this with numeric types, but some columns >> needed alpha chars eg 13mm. > > Two columns: > > data_val(numeric) data_unit(varchar) > 13 mm > >> >> Although I could have front ended this UI with mm nomenclature I did >> not. >> >> I'll put a table together with appropriate numeric types and see if >> the sort will behave. > > It will. > >> >> >> >> On 8/14/2021 9:21 AM, Adrian Klaver wrote: >>> On 8/14/21 9:14 AM, Bret Stern wrote: >>>> I have a table with metric, imperial, fraction columns. >>>> >>>> Is there a way to sort correctly using imperial (eg; .125, .375, >>>> .437 -> 1., 1.125) >>> >>> Alright how is this different from metric or fraction? >>> >>> I can sort of see fraction if you mean as 1/3, 1/20, etc. >>> >>> What is the data type of the field you are storing this in? >>> >>>> >>>> Couldn't handle it with ORDER BY ASC, DESC args so I added a >>>> sort_column and sorted based >>>> >>>> on those values eg; 1,2,3,4,5,6 indicating the value I need to show >>>> in which sequence. >>>> >>>> >>>> Just curious what the pro's do >>>> >>>> Bret >>>> >>>> >>>> >>> >>> > >
Adrian Klaver <adrian.klaver@aklaver.com> writes:
> On 8/14/21 9:37 AM, Bret Stern wrote:
>> I haven't explored doing this with numeric types, but some columns
>> needed alpha chars eg 13mm.
> Two columns:
> data_val(numeric) data_unit(varchar)
> 13 mm
It sounds like your data is a completely disorganized mess :-(.
You might be able to bring some semblance of coherence to it with
an extension like postgresql-unit [1], but it will take a lot of
effort to get the data into a representation that can sort reasonably.
There's no "easy button" here.
regards, tom lane
[1] https://github.com/df7cb/postgresql-unit
On Sat, 14 Aug 2021 at 19:03, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Adrian Klaver <adrian.klaver@aklaver.com> writes:
> On 8/14/21 9:37 AM, Bret Stern wrote:
>> I haven't explored doing this with numeric types, but some columns
>> needed alpha chars eg 13mm.
> Two columns:
> data_val(numeric) data_unit(varchar)
> 13 mm
It sounds like your data is a completely disorganized mess :-(.
You might be able to bring some semblance of coherence to it with
an extension like postgresql-unit [1], but it will take a lot of
effort to get the data into a representation that can sort reasonably.
There's no "easy button" here.
regards, tom lane
[1] https://github.com/df7cb/postgresql-unit
I'm not sure, but maybe a kind of cast function that cast everything into a reasonable common unit(Khm... millimeters) and sort on that? It seems relatively simple to pick up the value and unit from a string with a regexp. Admittedly the data would still be a mess.
Regards,
Sándor
On 8/14/21 10:19 AM, Sándor Daku wrote: > > I'm not sure, but maybe a kind of cast function that cast everything > into a reasonable common unit(Khm... millimeters) and sort on that? It > seems relatively simple to pick up the value and unit from a string with > a regexp. Admittedly the data would still be a mess. I see the potential for another crash landing:) : https://en.wikipedia.org/wiki/Mars_Climate_Orbiter#Cause_of_failure > > Regards, > Sándor -- Adrian Klaver adrian.klaver@aklaver.com
Of course it's a complete disorganized mess..
That'a the world I live in.
Best thing is, I'm walking away with some new strategies.
You guys rock
-------- Original message --------
From: Sándor Daku <daku.sandor@gmail.com>
Date: 8/14/21 10:19 AM (GMT-08:00)
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Adrian Klaver <adrian.klaver@aklaver.com>, Bret Stern <bret_stern@machinemanagement.com>, pgsql-general@lists.postgresql.org
Subject: Re: Sort question - Fractions, Metric etc
On Sat, 14 Aug 2021 at 19:03, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Adrian Klaver <adrian.klaver@aklaver.com> writes:
> On 8/14/21 9:37 AM, Bret Stern wrote:
>> I haven't explored doing this with numeric types, but some columns
>> needed alpha chars eg 13mm.
> Two columns:
> data_val(numeric) data_unit(varchar)
> 13 mm
It sounds like your data is a completely disorganized mess :-(.
You might be able to bring some semblance of coherence to it with
an extension like postgresql-unit [1], but it will take a lot of
effort to get the data into a representation that can sort reasonably.
There's no "easy button" here.
regards, tom lane
[1] https://github.com/df7cb/postgresql-unit
I'm not sure, but maybe a kind of cast function that cast everything into a reasonable common unit(Khm... millimeters) and sort on that? It seems relatively simple to pick up the value and unit from a string with a regexp. Admittedly the data would still be a mess.
Regards,
Sándor
I would write a stable function converting everything to metric (or imperial, depends on your preferences) and sort on the return of the function. Since unit conversion functions do not need to modify the database and should always return the same values for the same arguments, the function can be used within a query (that is the meaning of the keyword "STABLE") and you can use it for sorting stuff Regards On 8/14/2021 12:14 PM, Bret Stern wrote: > I have a table with metric, imperial, fraction columns. > > Is there a way to sort correctly using imperial (eg; .125, .375, .437 > -> 1., 1.125) > > Couldn't handle it with ORDER BY ASC, DESC args so I added a > sort_column and sorted based > > on those values eg; 1,2,3,4,5,6 indicating the value I need to show in > which sequence. > > > Just curious what the pro's do > > Bret > > -- Mladen Gogala Oracle DBA Tel: (347) 321-1217 Blog: https://dbwhisperer.wordpress.com
You might define new types for temperature, length, whatever, with suitable conversion, operation and creation functions. You’d be able to define how the new types participate in indexes, support directly sorting on them, so you package up this complexity and forget about it.
Either normalize everything to metric on creation, or have an enumeration or boolean flag to indicate whether the value is metric or imperial (choose this one if you want values to remember how they were created and thus how they will display, and to avoid rounding errors converting back to imperial for display).
Depends how many places you use them whether this is worth it. But it would be a good way to make this complexity idiot-proof if you’ll be using it all over. You’d be able to just add and multiply lengths and such without worrying how they were specified.
Looks like this might do what you need on cursory examination: https://github.com/df7cb/postgresql-unit
Either normalize everything to metric on creation, or have an enumeration or boolean flag to indicate whether the value is metric or imperial (choose this one if you want values to remember how they were created and thus how they will display, and to avoid rounding errors converting back to imperial for display).
Depends how many places you use them whether this is worth it. But it would be a good way to make this complexity idiot-proof if you’ll be using it all over. You’d be able to just add and multiply lengths and such without worrying how they were specified.
Looks like this might do what you need on cursory examination: https://github.com/df7cb/postgresql-unit
On Aug 14, 2021, 12:51 -0700, Gogala, Mladen <gogala.mladen@gmail.com>, wrote:
I would write a stable function converting everything to metric (or
imperial, depends on your preferences) and sort on the return of the
function. Since unit conversion functions do not need to modify the
database and should always return the same values for the same
arguments, the function can be used within a query (that is the meaning
of the keyword "STABLE") and you can use it for sorting stuff
Regards
On 8/14/2021 12:14 PM, Bret Stern wrote:I have a table with metric, imperial, fraction columns.--
Is there a way to sort correctly using imperial (eg; .125, .375, .437
-> 1., 1.125)
Couldn't handle it with ORDER BY ASC, DESC args so I added a
sort_column and sorted based
on those values eg; 1,2,3,4,5,6 indicating the value I need to show in
which sequence.
Just curious what the pro's do
Bret
Mladen Gogala
Oracle DBA
Tel: (347) 321-1217
Blog: https://dbwhisperer.wordpress.com
Here's the clip of the UI. The user selects whatever value the vendor provides for unit thickness. The data entry
people aren't comfortable converting.
At this point the sort_order column managed to do the trick....but going forward on my next
application, I can see a different approach.
Are you suggesting a function like this shellsort used with an array.
https://www.geeksforgeeks.org/shellsort/
// C++ implementation of Shell Sort
#include <iostream>
using namespace std;
/* function to sort arr using shellSort */
int shellSort(int arr[], int n)
{
// Start with a big gap, then reduce the gap
for (int gap = n/2; gap > 0; gap /= 2)
{
// Do a gapped insertion sort for this gap size.
// The first gap elements a[0..gap-1] are already in gapped order
// keep adding one more element until the entire array is
// gap sorted
for (int i = gap; i < n; i += 1)
{
// add a[i] to the elements that have been gap sorted
// save a[i] in temp and make a hole at position i
int temp = arr[i];
// shift earlier gap-sorted elements up until the correct
// location for a[i] is found
int j;
for (j = i; j >= gap && arr[j - gap] > temp; j -= gap)
arr[j] = arr[j - gap];
// put temp (the original a[i]) in its correct location
arr[j] = temp;
}
}
return 0;
}
void printArray(int arr[], int n)
{
for (int i=0; i<n; i++)
cout << arr[i] << " ";
}
int main()
{
int arr[] = {12, 34, 54, 2, 3}, i;
int n = sizeof(arr)/sizeof(arr[0]);
cout << "Array before sorting: \n";
printArray(arr, n);
shellSort(arr, n);
cout << "\nArray after sorting: \n";
printArray(arr, n);
return 0;
}
Array before sorting: 12 34 54 2 3 Array after sorting: 2 3 12 34 54
On 8/14/2021 12:51 PM, Gogala, Mladen wrote:
I would write a stable function converting everything to metric (or imperial, depends on your preferences) and sort on the return of the function. Since unit conversion functions do not need to modify the database and should always return the same values for the same arguments, the function can be used within a query (that is the meaning of the keyword "STABLE") and you can use it for sorting stuff
Regards
On 8/14/2021 12:14 PM, Bret Stern wrote:I have a table with metric, imperial, fraction columns.
Is there a way to sort correctly using imperial (eg; .125, .375, .437 -> 1., 1.125)
Couldn't handle it with ORDER BY ASC, DESC args so I added a sort_column and sorted based
on those values eg; 1,2,3,4,5,6 indicating the value I need to show in which sequence.
Just curious what the pro's do
Bret
Attachment
On 8/14/21 1:24 PM, Bret Stern wrote: > Here's the clip of the UI. The user selects whatever value the vendor > provides for unit thickness. The data entry So the vendors supply the measurements in all the various units for a given item? > > people aren't comfortable converting. > > At this point the sort_order column managed to do the trick....but going > forward on my next > > application, I can see a different approach. > > Are you suggesting a function like this shellsort used with an array. -- Adrian Klaver adrian.klaver@aklaver.com
Yep, I provide the UI for the user to select the vendor supplied unit value from the list. I don't want anyone entering (manually) any value, they must select from my list (fed from a PG table). (and since some units don't translate exactly, they pick the one that is closest). It's Tile (floor, shower etc), not the tightest tolerance of dimensions, so it's not catastrophic if it's off a 32nd or so. cheers On 8/14/2021 1:43 PM, Adrian Klaver wrote: > On 8/14/21 1:24 PM, Bret Stern wrote: >> Here's the clip of the UI. The user selects whatever value the vendor >> provides for unit thickness. The data entry > > So the vendors supply the measurements in all the various units for a > given item? > >> >> people aren't comfortable converting. >> >> At this point the sort_order column managed to do the trick....but >> going forward on my next >> >> application, I can see a different approach. >> >> Are you suggesting a function like this shellsort used with an array. > >
On 8/14/21 2:04 PM, Bret Stern wrote: > Yep, > > I provide the UI for the user to select the vendor supplied unit value > from the list. I should have been more specific, does the vendor do all the conversions and supply them to you? > > I don't want anyone entering (manually) any value, they must select from > my list (fed from a PG table). > > (and since some units don't translate exactly, they pick the one that is > closest). Yes, nominal dimensions. That is what I'm trying to work out, is this something that makes sense to the vendor and should not be tampered with? > > It's Tile (floor, shower etc), not the tightest tolerance of dimensions, > so it's not catastrophic if it's off Is it only tile you are working with? > > a 32nd or so. > > cheers > > > > -- Adrian Klaver adrian.klaver@aklaver.com
On 8/14/2021 2:13 PM, Adrian Klaver wrote: > On 8/14/21 2:04 PM, Bret Stern wrote: >> Yep, >> >> I provide the UI for the user to select the vendor supplied unit >> value from the list. > > I should have been more specific, does the vendor do all the > conversions and supply them to you? > Vendors supply dimensions, depending on where in the world the product comes from, could be metric, imperial or fraction >> >> I don't want anyone entering (manually) any value, they must select >> from my list (fed from a PG table). >> >> (and since some units don't translate exactly, they pick the one that >> is closest). > > Yes, nominal dimensions. That is what I'm trying to work out, is this > something that makes sense to the vendor and should not be tampered with? > The vendor is out of the picture at this point. They provide a catalog, and we enter (or import) items into our POS system. We match their dimension within tolerance. >> >> It's Tile (floor, shower etc), not the tightest tolerance of >> dimensions, so it's not catastrophic if it's off > > Is it only tile you are working with? No. Slabs eg granite, slate, dolemite, marble..cut from larger blocks and delivered in blocks of ten slabs normally, where tolerances are also +/- .032 I will say this business has been behind in the attributes game. Plus there are many "artsy" vendors who can hardly speak in these terms, and don't publish to us, so we do the best we can. Getting vendors to supply the basic values is a struggle. > >> >> a 32nd or so. >> >> cheers >> >> >> >> > > >
On 8/14/21 2:47 PM, Bret Stern wrote: > > On 8/14/2021 2:13 PM, Adrian Klaver wrote: >> On 8/14/21 2:04 PM, Bret Stern wrote: > Vendors supply dimensions, depending on where in the world the product > comes from, could be metric, imperial > > or fraction > >> Yes, nominal dimensions. That is what I'm trying to work out, is this >> something that makes sense to the vendor and should not be tampered with? >> > The vendor is out of the picture at this point. They provide a catalog, > and we enter (or import) items into our POS system. We match their > dimension within tolerance. > It would seem then a entry system that specifies the initial input unit(metric, imperial) and then does the conversion to a set unit(my preference would be metric) for storage to the table in that unit. Then you would convert on the fly to fill out the display field. Or you could use something like generated columns(https://www.postgresql.org/docs/12/ddl-generated-columns.html) available in Postgres 12+ to pre-fill columns to save the overhead on subsequent queries. For pre-12 maybe a trigger on the table column to fill in the other columns. In any case you sort(order by) by your canonical column which would be of numeric type. > No. Slabs eg granite, slate, dolemite, marble..cut from larger blocks > and delivered in blocks of ten slabs normally, where tolerances are > also +/- .032 > > I will say this business has been behind in the attributes game. Plus > there are many "artsy" vendors > > who can hardly speak in these terms, and don't publish to us, so we do > the best we can. > > Getting vendors to supply the basic values is a struggle. > >> >>> >>> a 32nd or so. >>> >>> cheers >>> >>> >>> >>> >> >> >> > > -- Adrian Klaver adrian.klaver@aklaver.com
On 15 Aug 2021, at 7:47, Bret Stern wrote: > I will say this business has been behind in the attributes game. Plus there are many "artsy" vendors who can hardly speakin these terms, and don't publish to us, so we do the best we can. > > Getting vendors to supply the basic values is a struggle. > I suspect you have already found all vendors reliably supply two values: how many (a dimensionless parameter), and how much(mostly in units of dollar) 😉 After that there may be length width and thickness values specified with (hopefully) the same units ± weight (per unit orbox?) ± allowance for grout/joining (in case your application is going to be used as an aid in estimating quantities needed) The truly artistic supplier will refrain from making anything the same and your customer will be expected to buy the itemfirst and plan their project around it. One possible data wrangling scheme would be to give each item a catalogue number (yours) — Just noticed Adrain saying somethingsimilar so apologies for the overlap. The vendor_spec table would capture the vendor’s identifier, description and supplied dimensions (with a units column). The dimensions_view (suggest a materialised view) would carry the dimension information in converted form, e.g., mm: numeric(8,3) Once the conversions are setup the customer can be supplied with dimensions in the system of their choice and you have asensible common point reference for any rankings. When I first saw this thread I thought you were getting into metal dimensions and started to wonder if your next phase wasto “organise” bolts according to diameter length and thread… you have picked the easier course, the other is a nightmare,e.g., https://www.americanmachinetools.com/machinist_tables.htm Gavan Schneider —— Gavan Schneider, Sodwalls, NSW, Australia Explanations exist; they have existed for all time; there is always a well-known solution to every human problem — neat,plausible, and wrong. — H. L. Mencken, 1920
On 8/14/21 4:05 PM, Gavan Schneider wrote: > On 15 Aug 2021, at 7:47, Bret Stern wrote: > >> I will say this business has been behind in the attributes game. Plus there are many "artsy" vendors who can hardly speakin these terms, and don't publish to us, so we do the best we can. >> >> Getting vendors to supply the basic values is a struggle. >> > I suspect you have already found all vendors reliably supply two values: how many (a dimensionless parameter), and howmuch (mostly in units of dollar) 😉 Hmm, you are lucky:) Of late I get 'who knows' for how many and 'we'll see' for how much. > Gavan Schneider > —— > Gavan Schneider, Sodwalls, NSW, Australia > Explanations exist; they have existed for all time; there is always a well-known solution to every human problem — neat,plausible, and wrong. > — H. L. Mencken, 1920 > > -- Adrian Klaver adrian.klaver@aklaver.com
> I have a table with metric, imperial, fraction columns. Have "metric_equivalent_magnitude" and "metric_equivalent_unit" columns and do all your sorting &c. via those columns. Use the relatively new generated (or computed/calculated) columns to calculate these. That way, you get the conversion correct once and you don't have to worry about it any more! That way, you can compare between cubits, furlongs and kilometres or barrels, hogsheads and litres & American and British imperial units. You might also want a metric_equivalent_type to ensure that you're not comparing lengths with volumes, masses or velocities... &c...? Pól...