2020-06-24

VBA 丸めのまとめ

VBA(Visual Basic for Application)における数値の丸めについてまとめておきます。

偶数丸め(JIS丸め、銀行丸め)

VBA標準のRound関数は偶数丸め(JIS丸め、銀行丸め)なのでそのまま使えるのですが、整数部での丸め(小数部桁数に負の値を指定する)ができません。以下の関数を作り、拡張します。

' 偶数丸め(整数部丸め対応)
Public Function RoundHalfEven(ByVal number As Double, Optional ByVal digitsAfterDecimal As Long = 0) As Double
    If digitsAfterDecimal < 0 Then
        Dim shift As Double
        shift = 10 ^ digitsAfterDecimal
        RoundHalfEven = Round(number * shift) / shift
    Else
        RoundHalfEven = Round(number, digitsAfterDecimal)
    End If
End Function

四捨五入

Excel VBAの場合、ワークシート関数のRoundを使えばよいのですが、Access や VBScript では使えないので自作しておきます。

' 四捨五入
Public Function RoundHalfUp(ByVal number As Double, Optional ByVal digitsAfterDecimal As Long = 0) As Double
    Dim shift As Long
    shift = 10 ^ digitsAfterDecimal
    '浮動小数点誤差を避けるため十進型に変換する
    RoundHalfUp = Fix(CDec(number) * shift + 0.5 * sgn(number)) / shift
End Function

Decimal型はVBAでは宣言できないため、CDec関数で変換する必要があります。Currency 型は小数部4桁に制限されるため使用しません。

なお、Decimal型との演算はDecimal型に変換されて行われます。

? 1.2-1.1
 9.99999999999999E-02 
? CDec(0)+1.2-1.1
 0.1 

有効数字に丸め

有効数字に丸める場合です。以下は偶数丸めの場合で上述のRoundHalfEven関数を使用しています。四捨五入で丸める場合は、上述のRoundHalfUp関数に置き換えてください。
桁数の初期値は3桁にしてあります。

'有効数字に丸め(初期値3桁)
Public Function RoundSignificantFigures(ByVal number As Double, Optional ByVal digits As Long = 3) As Double
    If number = 0 Then
        RoundSignificantFigures = 0
    Else
        ' 偶数丸め
        RoundSignificantFigures = RoundHalfEven(number, digits - 1 - Int(Log(number) / Log(10)))
    End If
End Function

有効数字取得の考え方は、Oracleで有効数字を取得する。を参考にしてください。

床関数(Floor)

VBAのInt関数は床関数(Floor Function)と同じですので、そのまま使えます。床関数は実数x に対して x 以下の最大の整数と定義されます(Wikipedia
)が、Excelワークシート関数と同様に第2引数に基準値がとれるように拡張します。

' 床関数
Public Function Floor(ByVal number As Double, Optional ByVal significance As Double = 1) As Double
    If significance = 0 Or (number > 0 And significance < 0) Then
        Err.Raise 5 'プロシージャの呼び出し、または引数が不正です。
    End If
    Floor = Int(CDec(number) / significance) * significance
End Function

第1引数が正で第2引数が負の場合、正しい結果を返さないので、Excelのワークシート関数 Floorと態度を合わせてエラーにしています。

天井関数(Ceiling, Ceil)

天井関数(Ceiling, Ceil)は、数直線をイメージすると床関数を左右反転したものです。したがって、数値を正負反転して床関数で処理し、元に戻せばよいことになります。

' 天井関数
Public Function Ceiling(ByVal number As Double, Optional ByVal significance As Double = 1) As Double
    If significance = 0 Or (number > 0 And significance < 0) Then
        Err.Raise 5 'プロシージャの呼び出し、または引数が不正です。
    End If
    Ceiling = -Int(-CDec(number) / significance) * significance
End Function

2019-01-13

Oracleで有効数字を取得する。

基本的な考え方

ある数値の桁数を取得するには常用対数を用います。
FLOOR(LOG(10, n)) は次のとおり0以外の数値が最初に出現する桁を表します。

SQL> WITH DATA AS (
  2  SELECT 1234 N FROM DUAL
  3  UNION ALL SELECT 234 N FROM DUAL
  4  UNION ALL SELECT 34 N FROM DUAL
  5  UNION ALL SELECT 4 N FROM DUAL
  6  --UNION ALL SELECT 0 N FROM DUAL
  7  UNION ALL SELECT 0.123 N FROM DUAL
  8  UNION ALL SELECT 0.0234 N FROM DUAL
  9  UNION ALL SELECT 0.00345 N FROM DUAL
 10  )
 11  SELECT N, FLOOR(LOG(10, N)) FROM DATA;

         N FLOOR(LOG(10,N))
---------- ----------------
      1234                3
       234                2
        34                1
         4                0
      .123               -1
     .0234               -2
    .00345               -3

注: LOG(10, 0) は次のエラーを発生させます。: ORA-01428: 引数'0'が有効範囲外です

したがって、ROUND(n, - FLOOR(LOG(10, n))) で最初の1桁で丸めを行った値を取得することができます。

SQL> WITH DATA AS (
  2  SELECT 1234 N FROM DUAL
  3  UNION ALL SELECT 234 N FROM DUAL
  4  UNION ALL SELECT 34 N FROM DUAL
  5  UNION ALL SELECT 4 N FROM DUAL
  6  --UNION ALL SELECT 0 N FROM DUAL
  7  UNION ALL SELECT 0.123 N FROM DUAL
  8  UNION ALL SELECT 0.0234 N FROM DUAL
  9  UNION ALL SELECT 0.00345 N FROM DUAL
 10  )
 11  SELECT N, ROUND(n, - FLOOR(LOG(10, N))) FROM DATA;

         N ROUND(N,-FLOOR(LOG(10,N)))
---------- --------------------------
      1234                       1000
       234                        200
        34                         30
         4                          4
      .123                         .1
     .0234                        .02
    .00345                       .003

最初の d 桁で丸めるには、ROUND(n, d - 1- FLOOR(LOG(10, n)))とします。

関数

以上を関数にまとめると次のようになります。

CREATE OR REPLACE FUNCTION SIGNIFICANT_FIGURES(
    n   NUMBER,
    d   NUMBER
) RETURN NUMBER
IS
BEGIN
    IF n = 0 THEN
        RETURN 0;
    ELSE
        ROUND(n, d - 1 - FLOOR(LOG(10, n)));
    END IF;
END;

偶数丸めを行うには、ROUND 関数の代わりに ROUND_HALF_EVEN関数を使います。

注: Oracle Database 18c からROUND_TIES_TO_EVEN 関数が使えます。

2019-01-12

Oracleで偶数丸め(JIS丸め)

Oracle標準のROUND 関数は四捨五入です。
偶数丸め(JIS丸め、銀行丸め)を行うには関数を自作する必要があります。

基本的な考え方

単純な整数への丸めを行うケースを考えます。

  • 端数がちょうど0.5かどうかを判定するため、絶対値を1で割ります。
    余りが0.5のとき偶数丸めを行う必要がありますが、その他の場合は四捨五入と同じですので、ROUND関数が使えます。
  • 実は、偶数丸めを行うためには、絶対値を1でなく2で割った方が良いです。
    2で割った場合、余りが0.5または1.5のときに偶数丸めが必要となります。
    余りが0.5のときは切り捨て(TRUNCATE)を行い、 余りが1.5のときは切り上げ(四捨五入ですのでROUND関数が使えます)を行うことで偶数丸めが実現できます。

関数

以上を関数にまとめると、以下のようになります。

CREATE OR REPLACE FUNCTION ROUND_HALF_EVEN(
    n       NUMBER,
    integer NUMBER DEFAULT 0
) RETURN NUMBER
IS
BEGIN
    IF MOD(ABS(n) * POWER(10, integer), 2) = 0.5 THEN
        RETURN TRUNC(n, integer);
    ELSE
        RETURN ROUND(n, integer);
    END IF;
END;

Oracle Database 18c から ROUND_TIES_TO_EVEN 関数が実装されました。

2018-11-28

Get Significant Figures in Oracle

Basic Idea

You can use common logarithm (i.e. log base 10) to evaluate the number of digits.
FLOOR(LOG(10, n)) indicates the decimal places of the first non-zero digit as follows.

SQL> WITH DATA AS (
  2  SELECT 1234 N FROM DUAL
  3  UNION ALL SELECT 234 N FROM DUAL
  4  UNION ALL SELECT 34 N FROM DUAL
  5  UNION ALL SELECT 4 N FROM DUAL
  6  --UNION ALL SELECT 0 N FROM DUAL
  7  UNION ALL SELECT 0.123 N FROM DUAL
  8  UNION ALL SELECT 0.0234 N FROM DUAL
  9  UNION ALL SELECT 0.00345 N FROM DUAL
 10  )
 11  SELECT N, FLOOR(LOG(10, N)) FROM DATA;

         N FLOOR(LOG(10,N))
---------- ----------------
      1234                3
       234                2
        34                1
         4                0
      .123               -1
     .0234               -2
    .00345               -3

Note: LOG(10, 0) causes an error: ORA-01428: argument '0' is out of range

Therefore, you can get the value rounded to the first digit of a given number n by ROUND(n, - FLOOR(LOG(10, n))).

SQL> WITH DATA AS (
  2  SELECT 1234 N FROM DUAL
  3  UNION ALL SELECT 234 N FROM DUAL
  4  UNION ALL SELECT 34 N FROM DUAL
  5  UNION ALL SELECT 4 N FROM DUAL
  6  --UNION ALL SELECT 0 N FROM DUAL
  7  UNION ALL SELECT 0.123 N FROM DUAL
  8  UNION ALL SELECT 0.0234 N FROM DUAL
  9  UNION ALL SELECT 0.00345 N FROM DUAL
 10  )
 11  SELECT N, ROUND(n, - FLOOR(LOG(10, N))) FROM DATA;

         N ROUND(N,-FLOOR(LOG(10,N)))
---------- --------------------------
      1234                       1000
       234                        200
        34                         30
         4                          4
      .123                         .1
     .0234                        .02
    .00345                       .003

In order to round to first d digits, you can modify as ROUND(n, d - FLOOR(LOG(10, n)) - 1).

Function

These can be summarized as the following function.

CREATE OR REPLACE FUNCTION SIGNIFICANT_FIGURES(
    n   NUMBER,
    d   NUMBER
) RETURN NUMBER
IS
BEGIN
    IF n = 0 THEN
        RETURN 0;
    ELSE
        RETURN ROUND(n, d - FLOOR(LOG(10, n)) - 1);
    END IF;
END;

If you want to round half to even, use ROUND_HALF_EVEN function instead of the standard ROUND function.
Note: From Oracle Database 18c, you can use ROUND_TIES_TO_EVEN function.

2018-11-27

Round Half to Even in Oracle

Oracle standard ROUND function is rounding away from 0.
In order to round half to even (aka bankers’ rounding) in Oracle, you have to create a custom function.

Basic idea

Consider the case of rounding to an integer.

  • To judge whether the number is half-way between two integers, you have to divide the absolute value by 1 and see the remainder.
    If the remainder is 0.5, you need to round to even. Otherwise, you can use the standard ROUND function
  • Actually, you had better divide the absolute value by 2 rather than 1 in order to round half to even.
    In this case, you need to round to even when the remainder is 0.5 or 1.5.
    When the remainder is 0.5, you have to round towards 0 (i.e. truncate).
    When the remainder is 1.5, you have to round away from 0. (You can use the standard ROUND function.)

Function

These ideas can be summarized as the following function.

CREATE OR REPLACE FUNCTION ROUND_HALF_EVEN(
    n       NUMBER,
    integer NUMBER DEFAULT 0
) RETURN NUMBER
IS
BEGIN
    IF MOD(ABS(n) * POWER(10, integer), 2) = 0.5 THEN
        RETURN TRUNC(n, integer);
    ELSE
        RETURN ROUND(n, integer);
    END IF;
END;

From Oracle Database 18c, you can use ROUND_TIES_TO_EVEN function.

4

2018-11-19

ある文字を含むが、ある文字は含まない正規表現

ある文字を含むが、ある文字は含まない正規表現をネットで検索すると、
(以下、fooという文字を含み、barは含まない例)

/^(?!.*bar).*(?=foo).*$/

または、

/^(?=.*foo)(?!.*bar).*$/

がヒットするのですが、肯定先読みを使わない以下の書き方がパフォーマンスが良いようです。

/^(?!.*bar).*foo.*$/

参考

2018-02-28

Oracle 12c SCOTTユーザーの権限を確認する

前の記事「Oracle CONNECTおよびRESOURCEロールのまとめ」を書いていて、ふと気になったのですが、サンプル・スキーマのSCOTTユーザーの権限ははどうなっているのでしょうか。

SCOTTユーザーの権限は非推奨の CONNECTロールとRESOURCE ロールを使用していたはずです。Oracle12c では非推奨なので使われなくなっているのでしょうか。

サンプルスキーマの作成スクリプト ORACLE_HOME/rdbms/admin/utlsampl.sql を見ると、

GRANT CONNECT,RESOURCE,UNLIMITED TABLESPACE TO SCOTT IDENTIFIED BY tiger;

となっていました。

まだ、非推奨の RESOURCE ロールと CONNECT ロールが使われているようですね。
また、12c から RESOURCE ロールでは付与されなくなったUNLIMITED TABLESPACE 権限が別に付与されています。コメントに、

Rem mmoore 04/08/91 - use unlimited tablespace priv

と記載されていますので、だいぶ昔から RESOURCE ロールとは別に付与されていたようです。


データディクショナリで確認してみます。

SQL> select * from user_role_privs;

GRANTEE    GRANTED_ROLE
---------- -------------
SCOTT      RESOURCE
SCOTT      CONNECT
SQL> select * from user_sys_privs;

USERNAME  PRIVILEGE            
--------- ---------------------
SCOTT     UNLIMITED TABLESPACE


現セッションで利用できる権限を見てみます。

SQL> select * from session_privs;

PRIVILEGE
----------------------------------------
SET CONTAINER
CREATE INDEXTYPE
CREATE OPERATOR
CREATE TYPE
CREATE TRIGGER
CREATE PROCEDURE
CREATE SEQUENCE
CREATE CLUSTER
CREATE TABLE
UNLIMITED TABLESPACE
CREATE SESSION

昔、CONNECT ロールに付与されていた

  • ALTER SESSION
  • CREATE DATABASE LINK
  • CREATE SYNONYM
  • CREATE VIEW

は付与されてないですね。

非CDB環境で確認しているのですが、非CDBでは必要ないはずのコンテナの切替権限 SET CONTAINER が付与されています。
CONNECT ロールに新たに付与されているようです。

SQL> select * from role_sys_privs where ROLE = 'CONNECT';
ROLE                    PRIVILEGE
-------------------- --------------
CONNECT             SET CONTAINER
CONNECT             CREATE SESSION