尋找最適合的測量單位，以計算物質列表中的物質以不同（但兼容）的單位體積給出。

單位換算表

單位換算表儲存各種單位以及這些單位之間的關係：

id  unit          coefficient                 parent_id
36  "microlitre"  0.0000000010000000000000000 37
37  "millilitre"  0.0000010000000000000000000 5
5  "centilitre"  0.0000100000000000000000000 18
18  "decilitre"   0.0001000000000000000000000 34
34  "litre"       0.0010000000000000000000000 19
19  "dekalitre"   0.0100000000000000000000000 29
29  "hectolitre"  0.1000000000000000000000000 33
33  "kilolitre"   1.0000000000000000000000000 35
35  "megalitre"   1000.0000000000000000000000 0

按係數排序表明將parent_id子單元連結到其數字上級。

可以使用以下方法在 PostgreSQL 中創建此表：

CREATE TABLE unit_conversion (
 id serial NOT NULL, -- Primary key.
 unit text NOT NULL, -- Unit of measurement name.
 coefficient numeric(30,25) NOT NULL DEFAULT 0, -- Conversion value.
 parent_id integer NOT NULL DEFAULT 0, -- Relates units in order of increasing measurement volume.
 CONSTRAINT pk_unit_conversion PRIMARY KEY (id)
)

parent_id從to應該有一個外鍵id。

物質表

物質表列出了物質的具體數量。例如：

id  unit          label     quantity
1   "microlitre"  mercury   5
2   "millilitre"  water     500
3   "centilitre"  water     2
4   "microlitre"  mercury   10
5   "millilitre"  water     600

該表可能類似於：

CREATE TABLE substance (
 id bigserial NOT NULL, -- Uniquely identifies this row.
 unit text NOT NULL, -- Foreign key to unit conversion.
 label text NOT NULL, -- Name of the substance.
 quantity numeric( 10, 4 ) NOT NULL, -- Amount of the substance.
 CONSTRAINT pk_substance PRIMARY KEY (id)
)

問題

您將如何創建一個查詢來找到一個測量值，以使用具有整數（以及可選的實數分量）的最少數字來表示物質的總和？

例如，您將如何返回：

 quantity  unit        label
       15  microlitre  mercury 
      112  centilitre  water

但不是：

 quantity  unit        label
       15  microlitre  mercury 
     1.12  litre       water

因為 112 的實數比 1.12 少，而 112 比 1120 小。但在某些情況下，使用實數會更短——例如 1.1 升對 110 厘升。

大多數情況下，我無法根據遞歸關係選擇正確的單元。

原始碼

到目前為止，我有（顯然不工作）：

-- Normalize the quantities
select
 sum( coefficient * quantity ) AS kilolitres
from
 unit_conversion uc,
 substance s
where
 uc.unit = s.unit
group by
 s.label

想法

這是否需要使用 log 10來確定位數？

約束

這些單位並非都是十的冪。例如：http ://unitsofmeasure.org/ucum-essence.xml

這看起來很難看：

 with uu(unit, coefficient, u_ord) as (
   select
    unit, 
    coefficient,
    case 
     when log(u.coefficient) < 0 
     then floor (log(u.coefficient)) 
     else ceil(log(u.coefficient)) 
    end u_ord
   from
    unit_conversion u 
 ),
 norm (label, norm_qty) as (
  select
   s.label,
   sum( uc.coefficient * s.quantity ) AS norm_qty
 from
   unit_conversion uc,
   substance s
 where
   uc.unit = s.unit
 group by
   s.label
 ),
 norm_ord (label, norm_qty, log, ord) as (
  select 
   label,
   norm_qty, 
   log(t.norm_qty) as log,
   case 
    when log(t.norm_qty) < 0 
    then floor(log(t.norm_qty)) 
    else ceil(log(t.norm_qty)) 
   end ord
  from norm t
 )
 select
  norm_ord.label,
  norm_ord.norm_qty,
  norm_ord.norm_qty / uu.coefficient val,
  uu.unit
 from 
  norm_ord,
  uu where uu.u_ord = 
    (select max(uu.u_ord) 
     from uu 
     where mod(norm_ord.norm_qty , uu.coefficient) = 0);

但似乎可以解決問題：

|   LABEL | NORM_QTY | VAL |       UNIT |
-----------------------------------------
| mercury |   1.5e-8 |  15 | microlitre |
|   water |  0.00112 | 112 | centilitre |

你並不需要unit_conversion表中的父子關係，因為同一個家庭中的單元自然是按 的順序相互關聯的coefficient，只要你確定了家庭。

引用自：https://dba.stackexchange.com/questions/45246