te')); return $arr; } /* 遍历用户所有主题 * @param $uid 用户ID * @param int $page 页数 * @param int $pagesize 每页记录条数 * @param bool $desc 排序方式 TRUE降序 FALSE升序 * @param string $key 返回的数组用那一列的值作为 key * @param array $col 查询哪些列 */ function thread_tid_find_by_uid($uid, $page = 1, $pagesize = 1000, $desc = TRUE, $key = 'tid', $col = array()) { if (empty($uid)) return array(); $orderby = TRUE == $desc ? -1 : 1; $arr = thread_tid__find($cond = array('uid' => $uid), array('tid' => $orderby), $page, $pagesize, $key, $col); return $arr; } // 遍历栏目下tid 支持数组 $fid = array(1,2,3) function thread_tid_find_by_fid($fid, $page = 1, $pagesize = 1000, $desc = TRUE) { if (empty($fid)) return array(); $orderby = TRUE == $desc ? -1 : 1; $arr = thread_tid__find($cond = array('fid' => $fid), array('tid' => $orderby), $page, $pagesize, 'tid', array('tid', 'verify_date')); return $arr; } function thread_tid_delete($tid) { if (empty($tid)) return FALSE; $r = thread_tid__delete(array('tid' => $tid)); return $r; } function thread_tid_count() { $n = thread_tid__count(); return $n; } // 统计用户主题数 大数量下严谨使用非主键统计 function thread_uid_count($uid) { $n = thread_tid__count(array('uid' => $uid)); return $n; } // 统计栏目主题数 大数量下严谨使用非主键统计 function thread_fid_count($fid) { $n = thread_tid__count(array('fid' => $fid)); return $n; } ?>sql - DECIMAL with full precision SUM on QuestDB - Stack Overflow

科技改变生活-雨落星辰 - 所有的伟大,都源于一个勇敢的开始

    最新消息：雨落星辰是一个专注网站SEO优化、网站SEO诊断、搜索引擎研究、网络营销推广、网站策划运营及站长类的自媒体原创博客
    你的位置： 首页>programmer>sql - DECIMAL with full precision SUM on QuestDB - Stack Overflow

    sql - DECIMAL with full precision SUM on QuestDB - Stack Overflow

    programmeradmin4浏览0评论

    I have some finance numbers (as in 1234567891234567891234.1234567891234567) where I need to have full precision, and I cannot find how to achieve this on QuestDB, as both float and double would not be suitable.

    At the proposal of a core engineer at QuestDB slack I am storing now the integer part and decimal part as two different long numbers. But I cannot figure out how to use them for aggregations.

    CREATE TABLE test_sum (
    name SYMBOL,
    ts timestamp,
    price_integer_part long,
    price_decimal_part long
) TIMESTAMP(ts) PARTITION BY DAY WAL;

-- 10000.9998 + 10000.9998 = 20001.9996
INSERT INTO test_sum VALUES ('btc', '2023-07-14', '10000','9998');
INSERT INTO test_sum VALUES ('btc', '2023-07-14', '10000','9998');

    I have some finance numbers (as in 1234567891234567891234.1234567891234567) where I need to have full precision, and I cannot find how to achieve this on QuestDB, as both float and double would not be suitable.

    At the proposal of a core engineer at QuestDB slack I am storing now the integer part and decimal part as two different long numbers. But I cannot figure out how to use them for aggregations.

    CREATE TABLE test_sum (
    name SYMBOL,
    ts timestamp,
    price_integer_part long,
    price_decimal_part long
) TIMESTAMP(ts) PARTITION BY DAY WAL;

-- 10000.9998 + 10000.9998 = 20001.9996
INSERT INTO test_sum VALUES ('btc', '2023-07-14', '10000','9998');
INSERT INTO test_sum VALUES ('btc', '2023-07-14', '10000','9998');
    • sql
    • time-series
    • questdb
    Share Improve this question edited yesterday Javier Ramirez asked 2 days ago Javier RamirezJavier Ramirez 4,0081 gold badge27 silver badges36 bronze badges 6
    • 1 No decimal data type available? – jarlh Commented 2 days ago
    • 1 That seems a strange suggestion, maybe follow-up with the engineer on how they expect you to do this? – Andrew Commented 2 days ago
    • 1 What does "full precision" even mean? Does it mean you want to be able to store any real number without loss, even if it has a hundred or a thousand decimal places? And trying to store pi would crash the computer? You'll probably want to define a maximum for digits before and after the comma. Like: at least 15 digits before the comma and four after it. With this definition for instance you can use the data type long. You will store mills and have to divide by 1000 everytime you want to get to the base currency. – Thorsten Kettner Commented 2 days ago
    • @jarlh, unfortunaly not DECIMAL just yet. It is planned, but not as of right now. github/questdb/questdb/issues/4504 – Javier Ramirez Commented yesterday
    • @ThorstenKettner, edited the question. Originally I had to do calculations on numbers like this without losing precision on the decimals 1234567891234567891234.1234567891234567 – Javier Ramirez Commented yesterday
     |  Show 1 more comment

    1 Answer 1

    Reset to default 0

    After some fiddling, this works, but you need to know the number of decimal places. The result of the operation is in the last column, but I am leaving the rest of the columns just to see what we are calculating step by step.

    SELECT
    name,
    SUM(price_integer_part) + SUM(price_decimal_part) / 10000 AS total_price,
    FLOOR(SUM(price_decimal_part) / 10000) AS carry,
    SUM(price_decimal_part) % 10000 / 10000 AS new_decimal,
    SUM(price_decimal_part) % 10000::double / 10000 AS new_decimal_fraction,
    (SUM(price_integer_part) + SUM(price_decimal_part) / 10000) + (SUM(price_decimal_part) % 10000::double / 10000) as result
FROM test_sum;

    It gives the exact result of 20001.9996.

    The values of the intermediate columns are: total_price:20001, carry:1, new_decimal:0, new_decimal_fraction:0.9996

    与本文相关的文章

    发布评论

    评论列表(0)

    1. 暂无评论