Sequelize에서 그룹화를 사용하여 그룹 집계 쿼리 수행
- 高洛峰원래의
- 2016-12-28 11:30:463166검색
1. SQL의 그룹 쿼리와 Sequelize
1.1 SQL의 그룹 쿼리
SQL 쿼리에서는 GROUP BY 언어명을 통해 그룹 쿼리를 구현합니다. 그룹 쿼리를 완료하려면 GROUP BY 절을 집계 함수와 함께 사용해야 합니다. SELECT 쿼리 필드에서 집계 함수가 사용되지 않는 경우 ORDER BY 절에 나타나야 합니다. 쿼리를 그룹화한 후 쿼리 결과는 하나 이상의 열로 그룹화된 결과 집합입니다.
GROUP BY 구문
SELECT 列名, 聚合函数(列名) FROM 表名 WHERE 列名 operator value GROUP BY 列名 [HAVING 条件表达式] [WITH ROLLUP]
위 명령문에서:
집계 함수 - 그룹화 쿼리는 일반적으로 다음을 포함하는 집계 함수와 함께 사용됩니다.
COUNT()-레코드 수를 계산하는 데 사용
SUM()-필드 값의 합계를 계산하는 데 사용
AVG()-필드의 평균 값을 계산하는 데 사용
MAX - 쿼리 필드의 최대값을 찾는 데 사용됩니다.
MIX - 쿼리 필드의 최소값을 찾는 데 사용됩니다.
GROUP BY 하위 이름 - 쿼리 필드의 최소값을 찾는 데 사용됩니다. 그룹 필드
HAVING 하위 이름 - 그룹화 결과를 필터링하는 데 사용되며 조건식과 일치하는 결과가 표시됩니다.
WITH ROLLUP 하위 이름 - 레코드 추가를 지정하는 데 사용됩니다. 이전 데이터를 요약하는데 사용
1.2 Sequelize의 그룹 쿼리
집계 함수 사용
Sequelize는 모델에 대해 직접 집계 쿼리를 수행할 수 있는 집계 함수를 제공합니다.
aggregate(field, AggregateFunction, [options]) - 지정된 집계 함수로 쿼리
sum(field, [options]) - Sum
count( field, [options] ])-통계 쿼리 결과 개수
max(field, [options])-최대값 쿼리
min(field, [options])-최소값 쿼리
위 집계함수 중 options.attributes, options.attributes 속성을 통해 그룹화 관련 필드를 지정할 수 있고, options.having을 통해 필터 조건을 지정할 수 있으나, 파라미터에 대한 직접적인 지정은 없습니다. WITH ROLLUP 절.
예를 들어 .sum()을 사용하여 주문 수량이 1보다 큰 사용자의 주문 금액을 쿼리합니다.
Order.sum('price', {attributes:['name'], group:'name', plain:false, having:['COUNT(?)>?', 'name', 1]}).then(function(result){
console.log(result);
})생성된 SQL 문은 다음과 같습니다.
SELECT `name`, sum(`price`) AS `sum` FROM `orders` AS `Orders` GROUP BY name HAVING COUNT('name')>1;
집계 매개변수 사용
집계 함수를 직접 사용하는 것 외에도 findAll() 등의 메소드에 집계 쿼리 관련 매개변수를 지정하여 집계 쿼리를 구현할 수도 있습니다. 쿼리 시 options.attributes 및 options.attributes 속성을 통해 그룹화 관련 필드를 지정할 수도 있고, options.having을 통해 필터 조건을 지정할 수도 있습니다. 집계 함수 쿼리를 직접 사용하는 것과는 달리, 매개변수를 통해 집계 쿼리를 작성할 때 options.attributes 매개변수의 집계 필드를 배열 또는 객체 형태로 설정해야 하며, 집계 함수는 sequerize.fn을 통해 전달되어야 합니다. () 방법.
예를 들어 .findAll()을 사용하여 주문 수량이 1보다 큰 사용자의 주문 금액을 쿼리합니다.
Order.findAll({attributes:['name', [sequelize.fn('SUM', sequelize.col('price')), 'sum']], group:'name', having:['COUNT(?)>?', 'name', 1], raw:true}).then(function(result){
console.log(result);
})생성된 SQL 문은 다음과 같습니다.
SELECT `name`, sum(`price`) AS `sum` FROM `orders` AS `Orders` GROUP BY name HAVING COUNT('name')>1;
2. 사용 예
이제 주문 테이블의 데이터는 다음과 같습니다.
> select * from orders; +---------+-------------+--------+-----------+---------------------+ | orderId | orderNumber | price | name | createdOn | +---------+-------------+--------+-----------+---------------------+ | 1 | 00001 | 128.00 | 张小三 | 2016-11-25 10:12:49 | | 2 | 00002 | 102.00 | 张小三 | 2016-11-25 10:12:49 | | 4 | 00004 | 99.00 | 王小五 | 2016-11-25 10:12:49 | | 3 | 00003 | 199.00 | 赵小六 | 2016-11-25 10:12:49 | +---------+-------------+--------+-----------+---------------------+
2.1 간편 사용
그룹 쿼리를 사용하여 각 고객의 총 주문 금액을 계산합니다.
SQL 문을 사용하면 다음과 같이 쿼리할 수 있습니다.
> select name, SUM(price) from orders GROUP BY name; +-----------+------------+ | name | SUM(price) | +-----------+------------+ | 张小三 | 230.00 | | 王小五 | 99.00 | | 赵小六 | 199.00 | +-----------+------------+
Sequelize에서는 다음과 같이 쿼리할 수 있습니다.
Order.findAll({attributes:['sum', [sequelize.fn('SUM', sequelize.col('name')), 'sum']], group:'name', raw:true}).then(function(result){
console.log(result);
})
2.2 HAVING 절
을 사용하여 주문 수량이 1보다 큰 사용자의 총 주문 금액을 계산합니다.
SQL 문을 사용하면 다음과 같이 구현할 수 있습니다.
> select name, SUM(price) from orders GROUP BY name HAVING count(1)>1; +-----------+------------+ | name | SUM(price) | +-----------+------------+ | 张小三 | 230.00 | | 赵小六 | 199.00 | +-----------+------------+
Sequelize를 사용하면 다음과 같이 쿼리할 수 있습니다.
Order.findAll({attributes:['sum', [sequelize.fn('SUM', sequelize.col('name')), 'sum']], group:'name', having:['COUNT(?)>?', 'name', 1], raw:true}).then(function(result){
console.log(result);
})
2.3 WITH ROLLUP 절 사용
WITH ROLLUP 절은 MySQL 5.5+의 새로운 기능으로, 요약하는 데 사용됩니다. 통계 결과 . 그러나 이 글을 게시하는 시점에는 Sequelize가 아직 이 기능을 지원하지 않습니다.
전체 통계 열 추가:
> select name, SUM(price) from orders GROUP BY name WITH ROLLUP; +-----------+------------+ | name | SUM(price) | +-----------+------------+ | 张小三 | 230.00 | | 王小五 | 99.00 | | 赵小六 | 199.00 | | NULL | 528.00 | +-----------+------------+
2.4 연결 쿼리 및 그룹화
관리, 우리는 다른 테이블에 다른 정보를 저장합니다. 예를 들어, 한 테이블에는 주문 정보를 넣고 다른 테이블에는 고객 정보를 넣습니다. 관련된 두 테이블의 경우 조인 쿼리를 사용하여 관련 데이터를 찾습니다. 조인 쿼리를 수행할 때 집계 함수도 사용할 수 있습니다.
주문 테이블은 다음과 같습니다.
> select * from orders; +---------+-------------+--------+------------+---------------------+ | orderId | orderNumber | price | customerId | createdOn | +---------+-------------+--------+------------+---------------------+ | 1 | 00001 | 128.00 | 1 | 2016-11-25 10:12:49 | | 2 | 00002 | 102.00 | 1 | 2016-11-25 10:12:49 | | 3 | 00003 | 199.00 | 4 | 2016-11-25 10:12:49 | | 4 | 00004 | 99.00 | 3 | 2016-11-25 10:12:49 | +---------+-------------+--------+------------+---------------------+
고객 테이블 구성은 다음과 같습니다.
> select * from customers; +----+-----------+-----+---------------------+---------------------+ | id | name | sex | birthday | createdOn | +----+-----------+-----+---------------------+---------------------+ | 1 | 张小三 | 1 | 1986-01-22 08:00:00 | 2016-11-25 10:16:35 | | 2 | 李小四 | 2 | 1987-11-12 08:00:00 | 2016-11-25 10:16:35 | | 3 | 王小五 | 1 | 1988-03-08 08:00:00 | 2016-11-25 10:16:35 | | 4 | 赵小六 | 1 | 1989-08-11 08:00:00 | 2016-11-25 10:16:35 | +----+-----------+-----+---------------------+---------------------+
조인 쿼리와 그룹 쿼리를 이용하여 각 고객의 총 주문 금액을 계산합니다.
다음과 같은 SQL 문을 사용하여 쿼리합니다.
> select c.name, SUM(o.price) AS sum from customers AS c INNER JOIN orders AS o ON o.customerId =c.id GROUP BY c.name;
Sequelize에서 연결 쿼리를 수행할 때 먼저 연결을 설정해야 합니다. 모델 간:
Order.belongsTo(Customer, {foreignKey: 'customerId'});
연결 쿼리 및 그룹화:
var include = [{
model: Customer,
required: true,
attributes: ['name'],
}]
Order.findAll({include:include, attributes:[[sequelize.fn('SUM', sequelize.col('price')), 'sum']], group:'Customer.name', having:['COUNT(?)>?', 'name', 1], raw:true, rollup:true}).then(function(result){
console.log(result);
})
요약
이상은 이 글의 전체 내용입니다. 모든 분들의 공부나 업무에 조금이라도 도움이 되었으면 좋겠습니다. 궁금한 점이 있으시면 메시지를 남겨주세요.
Sequelize에서 그룹 집계 쿼리를 수행하기 위해 그룹별을 사용하는 것과 관련된 더 많은 기사를 보려면 PHP 중국어 웹사이트를 주목하세요!