Unit in Last Place

Sources

[1] general introduction for floating point error: https://www.cs.ovgu.de/~elkner/ASM/sparc-6.html#:~:text=Since%20fp%20representations%20always%20include,big%20can%20relative%20error%20get?

[2] IEEE floating point representation (for single precision) https://en.wikipedia.org/wiki/Single-precision_floating-point_format

[3] Gemini with confirmed sources but indirect quote

[4] simple examples: https://en.wikipedia.org/wiki/Unit_in_the_last_place#:~:text=//%20%CF%80%20with%2020%20decimal,:%200x1.0p%2D51)

[5] reference sheet: 

https://www.emmtrix.com/wiki/ULP_Difference_of_Float_Numbers#:~:text=The%20Unit%20in%20the%20Last,the%20nearest%20floating%2Dpoint%20number.

What

Unit in Last Place or ULP in short, is a common measure for floating point representation error in relative terms.

ULP itself is simply, for given exponent, quite literally unit of the last representable digit, or mathematically[1]:

B^(-(p-1))* B^e,

where e is the exponent of the particular number being represented, B is base and p is precision or significand of the represented value.

In most cases, floating point representation is directly tied to binary system for digitical logics, hence the above definition simplified to[3]:

where "number t" means the numerical value of the representation is t.

Mantissa vs. Significand

Note that for a double precision, S-E-M bitwidths are 1-11-52 and it brings up the point, though mantissa is often used interchangeably with significand, here we clarify:

p = 1.M [2]

or Mantissa is the fractional part of significand; this tracks with IEEE standard, since normal values have an implied 1 in significand and the bit pattern only records mantissa.

It's worth pointing out Mantissa historically defines the fractional part of a logrithm[3], hence the modern computer science definition is really a misnomer, as IEEE mantissa represents  "negative" part relative to current E. When describing precision of a floating point number, use only significand.

Why

As mentioned, we use ULP as a measure for relative error for fp[1]:

Every fp representation has an error associated with it. It is attempting to represent some true value, z, with an fp representation, f. The absolute error then is:

| f - z |

A measure of error that does not depend on the size of the number being represent is called relative error. Relative error is defined as:

| f - z | / z

In practice we can't handle real number, so z above is really the "golden" precision. Since it only makes sense to measure low precision fp against higher ones in digital logic, I will use ULP loss interchangeably with ULP error, and I propose a practical ULP loss to be:

| upgrade_cast(f) - z | / ULP(z)

and we follow the defnition to compute ulp loss as:

Obviously for a true real number, the ULP can be arbitrarily small, hence ULP error against real values can be arbitrarily large; while on the other end, the most accurate fp representation, with proper rounding, can resolve a value in representable range to leq 0.5 ULP loss.

For f, z both being fp, i.e. ULP is quantized to units of 1, because we can't possibly specify rounding rule per datum, a loss of 1 ULP is good enough.

How

Simple examples seen in [4], and C implementation guide is shown in [5].

Since 3.9, python provides a direct math methodhttps://docs.python.org/3/library/math.html#math.ulp

More

Read [1] for how fp produces error during computations.

Oracle中有三个表A、B、C A表结构: YBM TRACCODG DRUG_CODE DRUG_NAME NAME ID_NO B表结构: TRACCODG DOCUMENT_NO_TR PRESC_DATE PRESC_NO DOCUMENT_NO ITEM_NO DRUG_CODE DRUG_SPEC PACKAGE_SPEC DRUG_NAME FIRM_ID TYPE_CODE TYPE_NAME QUANTITY INSERT_TIME DRUG_FROM DRUG_TO ZY_FLAG EXPIRE_DATE BATCH_CODE C表结构: PATIENT_ID INP_NO NAME NAME_PHONETIC SEX DATE_OF_BIRTH BIRTH_PLACE CITIZENSHIP NATION ID_NO IDENTITY CHARGE_TYPE UNIT_IN_CONTRACT MAILING_ADDRESS ZIP_CODE PHONE_NUMBER_HOME PHONE_NUMBER_BUSINESS NEXT_OF_KIN RELATIONSHIP NEXT_OF_KIN_ADDR NEXT_OF_KIN_ZIP_CODE NEXT_OF_KIN_PHONE LAST_VISIT_DATE VIP_INDICATOR CREATE_DATE OPERATOR SERVICE_AGENCY BUSINESS_ZIP_CODE PHOTO PATIENT_CLASS DEGREE RACE RELIGION MOTHER_LANGUAGE FOREIGN_LANGUAGE ID_TYPE VIP_NO E_NAME NATIVE_PLACE MAILING_ADDRESS_CODE HEALTHY_CARD_NO MAILING_STREET_CODE CARD_SERIAL_NO TRANNO BANK_ACCOUNT_NO OCCUPATION CITIZEN_PLACE BANK CASH_TRACE_NO TERMINAL_ID BANKNO TRANSMONEY P_MARITAL_STATUS APP_FLOG QC_FLAG JKK SOURCE BUSINESS_NAME PASSPORT INSUR_NO GUARD_IDNO GUARD_IDTYPE GUARD_NAME PATIENT_TYPE WORKUNIT HOSPITAL_WORKER NOW_ADDR YB_YBZH GF_GYZH GF_FLAG NOW_ZIP_CODE UNIT_IN_FORCE MARITAL_STATUS ACCOUNT ALERGY INSURANCE_NO IMAGE JBBM NEXT_OF_BATH NEXT_OF_SEX NEXT_OF_ID INSURANCE_TYPE ID_NO_TYPE IDENTIFICATION_CODE PRIORITY VISIT_ID UNIT_NAME FEE_CODE DISABLE PM_ID PATIENT_CLASS_NO INFECTIOUSHISTORY ITEM_NO NEWHEALTHY_CARD_NO ARECODE PROVINCECODE CITYCODE COUNTYCODE TOWNSHIPCODE COMMUNITYCODE TEMAILING_ADDRESS_SUPPLEMENT 连接A、B、C 3个表格,其中A、B表用traccodg连接,A、C的name、id_no进行连接 查询出连接的表中存在的A表中的数据,并在B表中导出这些数据,用sql实现
最新发布
05-17
### SQL查询实现三张表的连接 为了满足需求,可以通过 `JOIN` 实现 A、B 和 C 三张表之间的关联,并筛选出符合条件的数据。以下是具体的SQL语句设计: #### 需求分析 1. **A 表与 B 表** 的连接条件为 `TRACCODG` 字段匹配。 2. **A 表与 C 表** 的连接条件为 `NAME` 和 `ID_NO` 同时匹配。 3. 查询目标是从 A 表中找到存在的记录,并从 B 表导出对应数据。 基于以上描述,可以构建如下 SQL 查询语句: ```sql SELECT DISTINCT A.TRACCODG, A.NAME, A.ID_NO, B.COLUMN_NAME_FROM_B AS COLUMN_NAME_FROM_B, -- 替换为实际需要的列名 ... FROM TABLE_A A LEFT JOIN TABLE_B B ON A.TRACCODG = B.TRACCODG LEFT JOIN TABLE_C C ON A.NAME = C.NAME AND A.ID_NO = C.ID_NO WHERE A.TRACCODG IS NOT NULL; ``` #### 解析说明 - 使用 `LEFT JOIN` 将 A 表分别与 B 表和 C 表进行连接[^3]。 - 条件 `A.TRACCODG = B.TRACCODG` 定义了 A 表与 B 表的关系。 - 条件 `(A.NAME = C.NAME AND A.ID_NO = C.ID_NO)` 定义了 A 表与 C 表的关系。 - 添加过滤条件 `A.TRACCODG IS NOT NULL` 确保只返回 A 表中有有效记录的结果[^1]。 此查询会返回 A 表中存在的记录及其对应的 B 表数据。 --- ### 导出特定数据到文件 如果希望将查询结果导出至外部文件,在 Oracle 中可使用以下方法之一完成操作: #### 方法一:通过 SQL*Plus 导出 执行以下命令序列: ```bash SET HEADING OFF SET PAGESIZE 0 SPOOL output.txt SELECT ... ; -- 上述完整的SQL语句 SPOOL OFF ``` 这将在当前目录下生成名为 `output.txt` 的文件,其中存储了查询结果。 #### 方法二:利用 PL/SQL 创建 CSV 文件 编写一段简单的PL/SQL脚本以创建CSV文件: ```plsql DECLARE v_file UTL_FILE.FILE_TYPE; BEGIN v_file := UTL_FILE.FOPEN('DIRECTORY_PATH', 'output.csv', 'W'); FOR rec IN ( SELECT TRACCODG, NAME, ID_NO, COLUMN_NAME_FROM_B FROM ( SELECT DISTINCT A.TRACCODG, A.NAME, A.ID_NO, B.COLUMN_NAME_FROM_B FROM TABLE_A A LEFT JOIN TABLE_B B ON A.TRACCODG = B.TRACCODG LEFT JOIN TABLE_C C ON A.NAME = C.NAME AND A.ID_NO = C.ID_NO WHERE A.TRACCODG IS NOT NULL ) ) LOOP UTL_FILE.PUT_LINE(v_file, rec.TRACCODG || ',' || rec.NAME || ',' || rec.ID_NO || ',' || rec.COLUMN_NAME_FROM_B); END LOOP; UTL_FILE.FCLOSE(v_file); END; / ``` 注意需提前配置好 Oracle 的目录对象 `DIRECTORY_PATH` 并赋予相应权限[^4]。 --- ### 注意事项 - 如果存在大量重复数据,则建议在最终查询前加入 `DISTINCT` 关键字去重。 - 若某些字段可能为空值,应考虑是否需要额外处理(如填充默认值)。
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值