java からSQLクエリを実行して取得した結果と、DBアプリ上で直にクエリを実行した結果が異なる

Question

お世話になります。

＝＝
SELECT
ROUND(AVG( 基準日終値*( CASE 基準年月
WHEN '202012' THEN 1 WHEN '202011' THEN 1 WHEN '202010' THEN 0.25
END )),2,1) AS 最小終値
FROM ～
＝＝

java（Servlet）で、上記のクエリで平均値を取得すると、
74.39　が返ります。このときgetBigDecimalで値を取得しています。
上記のクエリをDB（SQL Server Management Studio ）上で直に実行すると、
74.40が返ります。また、Excelで計算すると、やはり74.40が返ります。

何故、javaの場合、74.39が返るのでしょうか？
getBigDecimalで値を取得していますが、これを
getStringやgetDoubleに変更しても結果は同じでした。

ちなみに、平均値を算出する値は以下の通りです。
77.15
75.91
75.33
75.49
74.98
74.33
74.71
74.4
74.51
75.06
74.44
74.91
73.55
73.8
73.28
73.67
73.68
73.27
72.52
73.35
74.31
74.15

Answer 1

SQLServerには詳しくありません。あしからず。

• ROUND (Transact-SQL)
  • https://docs.microsoft.com/ja-jp/sql/t-sql/functions/round-transact-sql?view=sql-server-ver15

引数
...
length numeric_expression を丸める際の有効桁数です。
function 実行する操作の種類です。 ... 0 以外の値を指定した場合、... は切り捨てられます。

件のSQL。

SELECT ROUND(AVG(基準日終値*...),2,1) 

functionには1を渡しています。lengthで「切り捨て」ですね。

基準日終値の型がなんだかわかりませんが、件のデータをdoubleで計算すると、74.39999999999999です。切り捨てれば74.39。

何故、javaの場合、74.39が返るのでしょうか？

74.39999999999999を、切り捨てて74.39。

上記のクエリをDB（SQL Server Management Studio ）上で直に実行すると、74.40が返ります。

round関数の定義をみると、むしろ、この結果のほうが疑わしいのではないかと。

---

import org.junit.jupiter.api.Test
import java.math.BigDecimal

class HogeAvg {
  @Test
  fun aa() {
    val values: List<String> = data.trim().split("""\s+""".toRegex())
    println(values)

    val doubleAverage = values.map { it.toDouble() }.average()
    println("doubleAverage: ${doubleAverage}")
    // doubleAverage: 74.39999999999999

    val bigDecimalAverage =
      values
        .map { it.toBigDecimal() }
        .fold(BigDecimal.ZERO) { a, b -> a + b } / values.size.toBigDecimal()
    println("bigDecimalAverage: ${bigDecimalAverage}")
    // bigDecimalAverage: 74.40
  }

  val data = """
77.15
75.91
75.33
75.49
74.98
74.33
74.71
74.4
74.51
75.06
74.44
74.91
73.55
73.8
73.28
73.67
73.68
73.27
72.52
73.35
74.31
74.15
    """.trimIndent()
}

Comments

[20200812_javaer]

>shiketa様

ご回答頂きまして、有難うございます。

以下の点、補足させて頂きます。
SQLServerに登録されている「基準日終値」の型は、decimal(16, 6)です。
DB（SQL Server Management Studio ）上に直接クエリを記載し、
試しにROUNDせずに、AVG(基準日終値)だけで実行しても、74.4000000が返りました。

実際のPGでは、javaでは計算せずに、
ResultSet rs = stmt.executeQuery() でクエリを実行して、
rs.getBigDecimal でこの結果を変数に格納するだけなのですが、
変数には74.40が格納されます。
rs.getDouble、rs.getStringでも同じ結果です。

もう少し、私の方でも調査してみます。

Answer 2

SQLServerには詳しくありません。あしからず。

float型というのがあるんですね。
基準日終値の型 decimal(16, 6) と float だけのテーブルをつくってためしてみました。
create table test (decValue decimal(16,6), floatValue float)
結果。

select 
  avg(decValue) as _dec_avg,
  round(avg(decValue),2,1) as _dec_round,
  avg(floatValue) as _float_avg,
  round(avg(floatValue),2,1) as _float_round 
from test

avg: [(_dec_avg, 74.400000), (_dec_round, 74.400000), (_float_avg, 74.39999999999999), (_float_round, 74.39)]

件のSQLは、こう。

ROUND(
    AVG(基準日終値
     * (
        CASE 基準年月   
          WHEN '202012' THEN 1
          WHEN '202011' THEN 1
          WHEN '202010' THEN 0.25
        END
       )
    )
    ,2,1) AS 最小終値

基準年月が202010のとき、0.25 を掛けてます。この結果として、最小終値がfloat型になっているのではないでしょうか。

import java.sql.DriverManager

object HogeTest {
  @JvmStatic
  fun main(args: Array<String>) {
    val url = "jdbc:sqlserver://localhost"
    val conn = DriverManager.getConnection(url, "sa", "password@12345")
    println("conn: ${conn}")

    conn.createStatement().let { st ->
      val sql = """drop table if exists test"""
      println(sql)
      st.execute(sql)
      st.close()
    }

    conn.createStatement().let { st ->
      val sql = """create table test (decValue decimal(16,6), floatValue float)"""
      println(sql)
      st.execute(sql)
      st.close()
    }

    """insert into test values(?, ?)""".let { sql ->
      println(sql)
      conn.prepareStatement(sql).let { st ->
        dataList.trim().split("""\s+""".toRegex())
        .forEach { data ->
          st.setBigDecimal(1, data.toBigDecimal())
          st.setBigDecimal(2, data.toBigDecimal())
          st.execute()
        }
        st.close()
      }
    }

    conn.createStatement().let { st ->
      val sql = """select * from test"""
      println(sql)
      st.executeQuery(sql).let { rs ->
        while (rs.next()) {
          val row = (1..rs.metaData.columnCount).map { index ->
            rs.metaData.getColumnName(index) to rs.getString(index)
          }
          println("row: ${row}")
        }
        rs.close()
      }
      st.close()
    }

    println("----")

    conn.createStatement().let { st ->
      val sql = """
        select 
          avg(decValue) as _dec_avg,
          round(avg(decValue),2,1) as _dec_round,
          avg(floatValue) as _float_avg,
          round(avg(floatValue),2,1) as _float_round 
        from test""".trimIndent()
      println(sql)
      st.executeQuery(sql).let { rs ->
        while (rs.next()) {
          val row = (1..rs.metaData.columnCount).map { index ->
            rs.metaData.getColumnName(index) to rs.getBigDecimal(index)
          }
          println("avg: ${row}")
        }
        rs.close()
      }
      st.close()
    }

  }

  val dataList = """
77.15
75.91
75.33
75.49
74.98
74.33
74.71
74.4
74.51
75.06
74.44
74.91
73.55
73.8
73.28
73.67
73.68
73.27
72.52
73.35
74.31
74.15
    """.trimIndent()
}

conn: ConnectionID:1 ClientConnectionId: 6e2f8d13-0f16-4b7c-a773-7444fb6d5d3d
drop table if exists test
create table test (decValue decimal(16,6), floatValue float)
insert into test values(?, ?)
select * from test
row: [(decValue, 77.150000), (floatValue, 77.15)]
row: [(decValue, 75.910000), (floatValue, 75.91)]
row: [(decValue, 75.330000), (floatValue, 75.33)]
row: [(decValue, 75.490000), (floatValue, 75.49)]
row: [(decValue, 74.980000), (floatValue, 74.98)]
row: [(decValue, 74.330000), (floatValue, 74.33)]
row: [(decValue, 74.710000), (floatValue, 74.71)]
row: [(decValue, 74.400000), (floatValue, 74.4)]
row: [(decValue, 74.510000), (floatValue, 74.51)]
row: [(decValue, 75.060000), (floatValue, 75.06)]
row: [(decValue, 74.440000), (floatValue, 74.44)]
row: [(decValue, 74.910000), (floatValue, 74.91)]
row: [(decValue, 73.550000), (floatValue, 73.55)]
row: [(decValue, 73.800000), (floatValue, 73.8)]
row: [(decValue, 73.280000), (floatValue, 73.28)]
row: [(decValue, 73.670000), (floatValue, 73.67)]
row: [(decValue, 73.680000), (floatValue, 73.68)]
row: [(decValue, 73.270000), (floatValue, 73.27)]
row: [(decValue, 72.520000), (floatValue, 72.52)]
row: [(decValue, 73.350000), (floatValue, 73.35)]
row: [(decValue, 74.310000), (floatValue, 74.31)]
row: [(decValue, 74.150000), (floatValue, 74.15)]
----
select 
  avg(decValue) as _dec_avg,
  round(avg(decValue),2,1) as _dec_round,
  avg(floatValue) as _float_avg,
  round(avg(floatValue),2,1) as _float_round 
from test
avg: [(_dec_avg, 74.400000), (_dec_round, 74.400000), (_float_avg, 74.39999999999999), (_float_round, 74.39)]

最後に。DBeaverで件のDB接続した結果はこうなりました。おもいきり不審ですネ。

select 
  avg(decValue) as _dec_avg,
  round(avg(decValue),2,1) as _dec_round,
  avg(floatValue) as _float_avg,
  round(avg(floatValue),2,1) as _float_round 
from test
;

表示された検索結果
74.400000	74.400000	74.40	74.39

コピペした検索結果
74.400000	74.400000	74.39999999999999	74.39

どんなツールでSQLの実行結果を確認されたのかしりませんが、そのツールで表示されている結果は、見えている結果は、ホントウの値なのでしょうか？すくなくともDBeaverのデフォルト設定では、そうではないみたいです。

Comments

[20200812_javaer]

>shiketa様

何度もご検証頂きまして、有難うございます。
大変、参考になりました。

＞そのツールで表示されている結果は、見えている結果は、ホントウの値なのでしょうか

確かに、仰る通りですね。

私の方でも、もう少し調査と、これらの勉強をしていきます。
本当に、有難うございました。