GVKun编程网logo

如何在 postgresql 中的单个查询中选择一行并计算行？

2

此处将为大家介绍关于如何在postgresql中的单个查询中选择一行并计算行？的详细内容，此外，我们还将为您介绍关于centos7下源码编译安装php支持PostgreSQLpostgresql手册p

此处将为大家介绍关于如何在 postgresql 中的单个查询中选择一行并计算行？的详细内容，此外，我们还将为您介绍关于centos 7下源码编译安装php支持PostgreSQL postgresql手册 postgresql官网下载 postgresql视频教、database – 如何在一个查询中切换postgres中的布尔值、postgresql – Postgres函数与准备好的查询中的SQL注入、postgresql – Postgres计数在同一查询中具有不同的条件的有用信息。

本文目录一览：

如何在 postgresql 中的单个查询中选择一行并计算行？

如何在 postgresql 中的单个查询中选择一行并计算行？

如何解决如何在 postgresql 中的单个查询中选择一行并计算行？？

我在 postgresql 中有一个表如下：

 id | chat_id |  content  | time |                 read_times                 
----+---------+-----------+------+-------------------------------------------------------------------------
  1 | chat_10 | content_1 |  t1  | [{"username": "user1","time": 123},{"username": "user2","time": 111}]
  2 | chat_10 | content_2 |  t2  | [{"username": "user2","time": 1235}]
  3 | chat_10 | content_3 |  t3  | []
  4 | chat_11 | content_4 |  t4  | [{"username": "user1","time": 125},{"username": "user3","time": 121}]
  5 | chat_11 | content_5 |  t5  | [{"username": "user1","time": 126},"time": 127}]

注意：t1 < t2 < t3 < t4 < t5

每个用户阅读一条消息后，我们将其注册到 read_times 列中（user2 在时间 2 读取一条 ID 为 1235 的消息），现在我想得到具有未读计数聊天的用户聊天列表。 user1 的结果如下：

 chat_id |  content  |  unread_count
 --------+-----------+--------------
 chat_10 | content_3 |       2
 chat_11 | content_5 |       0

注意：unread_count 是用户未在 caht_id 中阅读的消息计数。

可以一次查询吗？

解决方法

首先，您必须使用 chat_id 函数提取每个 content 和 json_array_elements() 的用户名，并使用 FIRST_VALUE() 窗口函数获取每个 content 的最后一个 chat_id SUM()。
然后聚合并结合MAX()窗口函数和unread_count聚合函数得到列WITH cte AS ( SELECT t.chat_id,t.content,FIRST_VALUE(t.content) OVER (PARTITION BY t.chat_id ORDER BY t.time DESC) last_content,(r->>''username'') username FROM tablename t LEFT JOIN json_array_elements(read_times::json) r ON true ) SELECT DISTINCT c.chat_id,MAX(c.last_content) "content",SUM((MAX((COALESCE(username,'''') = ''user1'')::int) = 0)::int) OVER (PARTITION BY c.chat_id) unread_count FROM cte c GROUP BY c.chat_id,c.content ORDER BY c.chat_id ：

1 3
2 6
3 7
7 12
8

参见demo。

centos 7下源码编译安装php支持PostgreSQL postgresql手册 postgresql官网下载 postgresql视频教

centos 7下源码编译安装php支持PostgreSQL postgresql手册 postgresql官网下载 postgresql视频教

1. 下载源码

$ mkdir /usr/downloads
$ wget -c http://cn2.php.net/distributions/php-5.6.20.tar.gz
$ tar -xvf php-5.6.20.tar.gz
$ mv php-5.6.20 /usr/local/src
$ cd !$ &amp; cd php-5.6.20
登录后复制

2. 阅读安装指导

$ ls -also
$ less README
$ less INSTALL
登录后复制

3. 安装依赖包

$ yum install apr apr-util apr-devel apr-util-devel prce lynx
登录后复制

4. 安装httpd

$ wget -c http://apache.fayea.com//httpd/httpd-2.4.20.tar.gz
$ tar -xvf httpd-2.4.20.tar.gz
$ cd httpd-2.4.20
$ ./configure \
--prefix=/usr/local/programs/apache2 \
--enable-rewrite \
--enable-so \
--enable-headers \
--enable-expires \
--with-mpm=worker \
--enable-modules=most \
--enable-deflate \
--enable-module=shared
$ make
$ make install
$ cd /usr/local/programs/apache2
$ cp bin/apachectl /etc/init.d/httpd ## 复制启动脚本
$ /etc/init.d/httpd start ## 启动apache服务器,访问http://localhost/
$ egrep -v ''^[ ]*#|^$'' /usr/local/apache2/conf/httpd.conf | nl ## 查看apache服务器的配置
## 将apache加入系统服务
vi /etc/rc.d/rc.local
```
/usr/local/programs/apache2/bin/apachectl start
```
$ cat /etc/rc.local
登录后复制

4. 安装postgresql

立即学习“PHP免费学习笔记（深入）”；

$ yum install readline-devel  ## 安装readline依赖
$ cd /usr/downloads
$ wget -c https://ftp.postgresql.org/pub/source/v9.5.0/postgresql-9.5.0.tar.bz2
$ tar -xvf postgresql-9.5.0.tar.bz2
$ cd postgresql-9.5.0
$ ./configure --prefix=/usr/local/programs/postgresql
$ make
$ su
$ make install
$ /sbin/ldconfig /usr/local/programs/postgresql/lib ## 刷新下共享动态库
$ cd /usr/local/programs/postgresql
$ bin/psql --version  ## 检查运行情况
## 开始对postgresql的配置
$ vi /etc/profile.d/postgresql.sh ## 增加环境变量,不推荐直接在/etc/profile中添加,系统更新升级时会需要merge
``` 
PATH=/usr/local/programs/postgresql:$PATH
export PATH
```
$ source /etc/profile ## 更新环境变量

## 增加用户和其他文件夹
$ adduser postgres
$ passwd postgres 
$ mkdir /usr/local/programs/postgresql/logs
$ mkdir /usr/local/programs/postgresql/data
$ chown postgres /usr/local/programs/postgresql/data
$ su - postgres

## 初始化数据库
$ ./bin/initdb -D ./data
$ ./bin/createdb test
$ ./bin/psql test
## 已有数据库，可导入data文件夹后尝试root访问，假如带密码，可能需要进一步研究下
$ ./bin/postgres -D ./data &gt;./logs/start-log-1.log 2&gt;&amp;1 &amp;
$ ./bin/psql --list  ##列出数据库
## ok，安装完成

## 自定义设置，权限控制等，可以跳过，等熟悉使用后再做
## 编辑数据库配置及权限文件：
$ vi /usr/local/programs/postgresql/data/postgresql.conf   ## 数据库配置文件
$ chown postgres postgresql.conf
$ chmod 644 postgresql.conf
$ vi /usr/local/programs/postgresql/data/pg_hba.conf   ## 权限文件
$ vi /usr/local/programs/postgresql/data/pg_ident.conf

## 设置开机自启动：
$ vi /etc/rc.d/rc.local    ## 添加如下内容
```
/usr/local/programs/postgresql/bin/postgresql start
```
登录后复制

5. 安装php

## 源码已经在第一步中下载，现在开始安装:
$ yum install libxml2 libxml2-devel libpng libpng-devel libjpeg libjpeg-devel freetype freetype-devel
$ ./configure \
--prefix=/usr/local/programs/php \
--with-apxs2=/usr/local/programs/apache2/bin/apxs \
--with-zlib \
--with-gd \
--with-jpeg-dir \
--with-png-dir \
--with-freetype-dir \
--with-zlib-dir \
--enable-mbstring \
--with-pgsql=/usr/local/programs/postgresql \
--with-pdo-pgsql=/usr/local/programs/postgresql 
$ make
$ make test
&gt; Bug #42718 (unsafe_raw filter not applied when configured as default filter) [ext/filter/tests/bug42718.phpt]  XFAIL REASON: FILTER_UNSAFE_RAW not applied when configured as default filter, even with flags
&gt; Bug #67296 (filter_input doesn''t validate variables) [ext/filter/tests/bug49184.phpt]  XFAIL REASON: See Bug #49184
&gt; Bug #53640 (XBM images require width to be multiple of 8) [ext/gd/tests/bug53640.phpt]  XFAIL REASON: Padding is not implemented yet
&gt; zend multibyte (7) [ext/mbstring/tests/zend_multibyte-07.phpt]  XFAIL REASON: https://bugs.php.net/bug.php?id=66582
&gt; zend multibyte (9) [ext/mbstring/tests/zend_multibyte-09.phpt]  XFAIL REASON: https://bugs.php.net/bug.php?id=66582
&gt;Bug #70470 (Built-in server truncates headers spanning over TCP packets) [sapi/cli/tests/bug70470.phpt]  XFAIL REASON: bug is not fixed yet

## 查阅官方的bug，发现：
&gt; id=66582: status : Closed. Fixed in master (PHP7)
&gt; id=42718: status : Assigned
&gt; id=42718: reference to id=49184, unsolved for many years
## 那就不关心了，直接装吧
$ make install
&gt; You may want to add: /usr/local/programs/php/lib/php to your php.ini include_path

## 那就按它说的设置吧
$ cp php.ini-development /usr/local/programs/php/lib/php.ini
```
include_path = ".;/usr/local/programs/php/lib/php"

## 然后，编辑httpd的设置，确保其能正确解析php文件
```
...
LoadModule php5_module modules/libphp5.so
...
AddType application/x-httpd-php .php
AddType application/x-httpd-php-source .php5

...
<ifmodule dir_module>
    DirectoryIndex index.html index.php
</ifmodule>     
```

## 重启httpd，测试
$ cd /usr/local/programs/apache2
$ bin/httpd -h
$ bin/httpd -k stop
$ bin/httpd -f conf/httpd.conf
## 默认设置的www页面在./htdocs/下，那就先去里面建一个测试页面吧
$ vi htdocs/index.php
```
<?php phpinfo(); ?>
```
$ curl http://localhost/index.php |grep postgresql
#ok
登录后复制

后续应该做的事

* 1. 启动时，不需要要手动指定配置文件
* 2. php初始化www目录设置
* 3. php 用户、权限管理等

'').addClass(''pre-numbering'').hide(); $(this).addClass(''has-numbering'').parent().append($numbering); for (i = 1; i '').text(i)); }; $numbering.fadeIn(1700); }); });

以上就介绍了centos 7下源码编译安装php支持PostgreSQL，包括了postgresql,centos 7方面的内容，希望对PHP教程有兴趣的朋友有所帮助。

database – 如何在一个查询中切换postgres中的布尔值

database – 如何在一个查询中切换postgres中的布尔值

我正在尝试更新postgres表中的一行.我想切换一个布尔字段.

我想知道是否有一个查询来更新字段,而不是先检查值是什么并更新相反的字段.

我找到了MySQL的解决方案,但它不适用于postgres：

UPDATE table SET boolean_field = !boolean_field WHERE id = :id

 Error: Operator does not exist. ! boolean

postgres中有类似的语法吗？

解决方法

使用 NOT： 

UPDATE table SET boolean_field = NOT boolean_field WHERE id = :id

当旧值等于FALSE时,它变为TRUE,反之亦然. NULL字段不会翻转,没有什么可以翻转.

完整的例子：

CREATE TABLE test(id serial,boolean_field boolean);

INSERT INTO test(boolean_field) 
VALUES(null),(false),(true) 
RETURNING *;

并运行测试：

UPDATE test
SET boolean_field = NOT boolean_field 
RETURNING *;

postgresql – Postgres函数与准备好的查询中的SQL注入

postgresql – Postgres函数与准备好的查询中的SQL注入

在Postgres中,准备好的查询和用户定义的函数是否等同于防止sql注入的机制？
一种方法比另一种方法有特别的优势吗？
这取决于.

sql函数

使用LANGUAGE sql,答案通常是肯定的.

传递的参数被视为值,并且不能进行sql注入 – 只要您不从主体调用不安全的函数并传递参数.

PL / pgsql函数

对于LANGUAGE plpgsql,答案通常是肯定的.

但是,PL / pgsql允许动态sql,其中传递的参数(或部分)连接到查询字符串并使用EXECUTE执行.这可以将用户输入转换为sql代码并使sql注入成为可能.你不能从外面告诉函数体是否正确处理它.提供工具.

仅在您需要的地方使用动态sql.使用参数作为值的纯sql语句可以安全地防止sql注入,如sql函数.

对于动态sql,最好将值作为值传递：

> USING条款. Example.

在主体上无法进行sql注入.

如果在sql字符串中连接值,请使用：

> format() with format specifier %L. Example.
> quote_literal() or quote_nullable(). Example.

安全地包装单引号中的字符串,从而避免语法错误和sql注入.

应在sql字符串中作为标识符处理的进程参数：

> format() with format specifier %I. Example.
> quote_ident(). Example.
> a cast to a registered type – 表名的regclass：_tbl :: regclass. Example.

在需要时安全地用双引号括起字符串,从而避免语法错误和sql注入.

有关：

> Refactor a PL/pgSQL function to return the output of various SELECT queries
> Define table and column names as arguments in a plpgsql function?

永远不要只是从用户输入和执行构建一个字符串.这包括由用户直接传递或从系统目录中提取的标识符.在构建动态sql时,所有必须像用户输入一样对待并安全引用！

更多关于此相关答案中的性能影响：

> Function Performance

sql注入的基础知识：

> http://bobby-tables.com/

类似的注意事项适用于允许动态sql的其他服务器端语言.

postgresql – Postgres计数在同一查询中具有不同的条件

postgresql – Postgres计数在同一查询中具有不同的条件

编辑Postgres 9.3

我正在编写一个包含以下架构的报告：http://sqlfiddle.com/#!15/fd104/2

当前查询工作正常,如下所示：

基本上它是一个3表内连接.我没有提出这个查询,但是离开它的开发人员我想修改查询.如您所见,TotalApplication只根据a.agent_id计算总应用程序.您可以在结果中看到totalapplication列.我想要的是删除它并将totalapplication更改为新的两列.我想添加一个完成调查和partitalsurvey专栏.所以基本上这部分将成为

SELECT a.agent_id as agent_id,COUNT(a.id) as CompletedSurvey
FROM forms a WHERE  a.created_at >= '2015-08-01' AND 
a.created_at <= '2015-08-31' AND disposition = 'Completed Survey'
GROUP BY a.agent_id

我刚刚添加了AND disposition =’Completed Survey’但是我需要另一个用于partialsurvey的列,它与completedsurvey具有相同的查询,唯一的区别是

AND disposition = 'Partial Survey'

和

COUNT(a.id) as PartialSurvey

但我不知道在哪里放置查询或查询将如何.所以最终输出有这些列

agent_id,name,completedsurvey,partialsurvey,loginhours,applicationperhour,rph

一旦它没问题,那么applicationperhour和rph我可以自己修复它

如果我理解正确,您正在寻找过滤(条件)聚合： 
SELECT a.agent_id as agent_id,COUNT(a.id) filter (where disposition = 'Completed Survey') as CompletedSurvey,count(a.id) filter (where disposition = 'Partial Survey') as partial_survey
FROM forms a 
WHERE a.created_at >= '2015-08-01' 
  AND a.created_at <= '2015-08-31' 
GROUP BY a.agent_id;

编辑
对于旧版本(< 9.4),您需要使用case语句： 

SELECT a.agent_id as agent_id,COUNT(case when disposition = 'Completed Survey' then a.id end) as CompletedSurvey,COUNT(case when disposition = 'Partial Survey' then a.id end) as partial_survey
FROM forms a 
WHERE a.created_at >= '2015-08-01' 
  AND a.created_at <= '2015-08-31' 
GROUP BY a.agent_id;

关于如何在 postgresql 中的单个查询中选择一行并计算行？的介绍已经告一段落，感谢您的耐心阅读，如果想了解更多关于centos 7下源码编译安装php支持PostgreSQL postgresql手册 postgresql官网下载 postgresql视频教、database – 如何在一个查询中切换postgres中的布尔值、postgresql – Postgres函数与准备好的查询中的SQL注入、postgresql – Postgres计数在同一查询中具有不同的条件的相关信息，请在本站寻找。

本文标签：

上一篇需要将 mssql 备份文件（.bak 文件）导入 postgres（mssql备份数据库）

下一篇在 RDS 上执行来自 EMR pyspark 的 sql 查询

相关文章