postgresql - 以 RESTfull 方式计算数据库列中的空条目

Question

我正在使用 express.js 和 pg-promise 从 postgreSQL 数据库（timescaleDB）获取数据。获取表中的列不是问题。

数据以以下格式存储在表中：

 | time | measurement1 | measurement2 |
 |------+--------------+--------------|
 | .... |     0,2      |    0,55      |
 |------+--------------+--------------|
 | .... |     null     |    0,58      |

其中时间是顺序时间戳。

我正在尝试以这种方式创建一个端点来计算给定列中的空条目：

router.get('/:name/count', async (req, res) => {
    await db.one("SELECT count(*) FROM $1 WHERE $2 IS NULL"), ['mytable', req.params.name], c => c.count)
        .then(data => res.status(200).send(data))
        .catch(err => console.log(err));
});

目的是创建用于分析存储在表中的数据的端点，使用带有count、avg、max、min等的 SQL 查询。

url 中的名称对应于我数据库中的一个表列。

如果我使用 URL '/' 或 /:name，但不使用 '/:name/count'，则此方法有效，然后出现错误：

错误：类型间隔的无效输入语法：“”计数“”

到目前为止，我的解决方案是在 pg-promise 任务中返回计数：

router.get('/:name', async (req, res) => {
    db.task(async t => {
        const data = await t.any("SELECT $1:name, $2:name FROM $3:name ORDER BY($2:name)", [req.params.name, 'time', 'mytable',]);
        const numOfNulls = await db.one("SELECT count(*) FROM $1:name WHERE $2:name IS NULL", ['mytable', req.params.name], c => c.count);
        return {data, numOfNulls}
    })
        .then(({data, numOfNulls}) => res.status(200).send({data, numOfNulls}))
        .catch(err => console.log(err))
});

这可行，但是当我想要一个单独的端点来获取一列时，它不是最理想的。

如何为计数端点构建正确的 URL？

编辑：

我还有一个带有 url /:name/:decimation的端点，可与 timescaleDB time_bucket() 函数一起使用，其中抽取是“1m”、“5m”、“1h”等。当我注释掉这个端点时，端点与/:name/count一起工作。

我怎样才能保留这两个端点？

标签： postgresqlrestexpresspg-promisetimescaledb

Answer 1

您的代码有一些异步/链语法混淆，而且您没有正确转义值。正确的代码应如下所示：

router.get('/:name/count', async (req, res) => {
 try {
      const {count} = await db.one('SELECT count(*) FROM $1:name WHERE $2:name IS NULL',
                                     ['mytable', req.params.name]);
      res.status(200).send(count);
  } catch(err) {
      console.log(err);
  }
});

而且您的解决方法看起来也错误 - 它db在任务中使用了错误的数据库上下文，而它应该是t：

router.get('/:name', async (req, res) => {
    try {
           const result = await db.task(async t => {
              const data = await t.any('SELECT $1:name, $2:name FROM $3:name ORDER BY($2:name)', [req.params.name, 'time', 'mytable']);
              const {count:numOfNulls} = await t.one('SELECT count(*) FROM $1:name WHERE $2:name IS NULL', ['mytable', req.params.name]);
              return {data, numOfNulls};
           });
           res.status(200).send(result));
        });
   } catch(err) {
        console.log(err);
   }