CAP Node.js SQL 집계 한 줄로 끝 — COUNT·SUM·GROUP BY 실전 패턴 #shorts #SAP #CAPforNode

왜 CAP에서 SQL 집계가 중요한가

판매 대시보드, 주문 통계, 지역별 매출 분석 — 비즈니스 백엔드의 절반은 결국 "집계"입니다. CAP for Node.js에서는 cds.ql이 CQL(Core Query Language)을 통해 GROUP BY, HAVING, COUNT, SUM 같은 SQL 집계 표현을 자바스크립트 안에서 자연스럽게 작성하도록 지원합니다. 핵심은 late materialization입니다. 즉, 집계 연산은 DB 레벨에서 끝내고 결과만 가져와야 하며, 전체 행을 클라이언트로 로딩한 뒤 자바스크립트 reduce()로 합산하는 안티패턴은 일반적으로 피해야 합니다.

이번 글의 학습 체크리스트

• cds.ql의 groupBy() 3가지 인자 형식 차이 이해
• COUNT/SUM/AVG/MIN/MAX 집계 함수 작성법 숙지
• HAVING과 WHERE의 적용 시점 구분
• 서브쿼리로 집계 결과를 다시 필터링하는 패턴 익히기
• 실무 최적화 — N+1 회피, 인덱스 고려

선수 지식 — cds.ql SELECT 리마인더

본격 집계 전에, 기존 글 "cds.ql CQN 쿼리 — SELECT/INSERT/UPDATE/DELETE 실전"에서 다룬 기본형을 한 번 짚고 갑니다. cds.ql의 SELECT는 tagged template literal 또는 fluent API 양쪽을 지원합니다.

const cds = require('@sap/cds')
const { Orders } = cds.entities('my.sales')

// 기본 조회
const all = await SELECT.from(Orders)

// WHERE 절 (tagged template)
const region = 'EMEA'
const filtered = await SELECT.from(Orders).where`region = ${region}`

// 컬럼 선택 + 정렬
const top = await SELECT.from(Orders)
  .columns('ID', 'amount', 'region')
  .orderBy`amount desc`
  .limit(10)

여기까지가 "행 단위 조회"입니다. 집계는 여기서 한 단계 위 — 여러 행을 하나의 그룹 값으로 압축하는 연산입니다.

환경 / 버전 / 준비물

• Node.js 20 LTS 이상 권장
• @sap/cds 7.x 또는 8.x (CAP Node.js)
• 로컬 DB는 @cap-js/sqlite, 운영은 SAP HANA Cloud (@cap-js/hana)
• 샘플 CDS 모델 (이번 튜토리얼 공통):

namespace my.sales;
using { cuid } from '@sap/cds/common';

entity Orders : cuid {
  customer  : Association to Customers;
  product   : Association to Products;
  amount    : Decimal(15,2);
  region    : String(50);
  status    : String(20);
  orderDate : Date;
}

entity Customers : cuid {
  name   : String(100);
  region : String(50);
}

entity Products : cuid {
  name  : String(100);
  price : Decimal(15,2);
}

핵심 개념 — CQL 집계와 Late Materialization

CAP의 cds.ql은 CQL(Cloud Query Language)을 자바스크립트로 표현한 DSL입니다. 표준 SQL과 거의 1:1로 매핑되지만, CAP 서비스 레이어와 통합되어 권한(@restrict), 핸들러(before/after), 텐시언트 분리까지 한 번에 처리됩니다.

집계 처리에서 가장 중요한 사고방식은 "DB가 잘하는 일은 DB에 시킨다"는 원칙입니다. 비유하자면, 100만 건의 영수증을 창고(DB)에서 사무실(Node.js)로 다 옮겨와서 계산기 두드리는 게 아니라, 창고 직원에게 "지역별 합계만 알려줘"라고 요청하는 것이죠. 이를 late materialization이라 부릅니다.

// 안티패턴 — 전체 행을 메모리로 끌고 와서 JS에서 집계
const all = await SELECT.from(Orders)        // 100만 건 로딩
const total = all.reduce((s, o) => s + o.amount, 0)  // 메모리 폭발

// 권장 패턴 — DB에서 집계
const [{ total }] = await SELECT.from(Orders)
  .columns`SUM(amount) as total`

groupBy는 세 가지 인자 형식을 모두 지원합니다.

형식	예시	특징
Tagged template	.groupBy`region, status`	SQL 친화적, 가독성 우수
문자열 인자	.groupBy('region', 'status')	동적 컬럼 조립에 유리
CXN 객체	.groupBy({ref:['region']})	프로그래매틱 빌드 시 사용

1단계 — GROUP BY 기본 패턴

가장 단순한 형태부터 시작합니다. "지역별 주문 건수"를 구해봅시다.

const cds = require('@sap/cds')

module.exports = cds.service.impl(async function () {
  const { Orders } = this.entities

  // 지역별 주문 건수
  this.on('regionStats', async () => {
    return await SELECT.from(Orders)
      .columns`region, COUNT(*) as cnt`
      .groupBy`region`
      .orderBy`cnt desc`
  })
})

3가지 인자 형식을 비교해봅니다. 결과는 동일하지만 동적 쿼리 빌드 상황에서 선택지가 달라집니다.

// (a) tagged template — 가장 직관적
SELECT.from(Orders).groupBy`region, status`

// (b) 문자열 인자 — 컬럼 목록을 배열로 다룰 때
const cols = ['region', 'status']
SELECT.from(Orders).groupBy(...cols)

// (c) CXN 객체 — 프레임워크/제너레이터에서 빌드
SELECT.from(Orders).groupBy(
  { ref: ['region'] },
  { ref: ['status'] }
)

2단계 — 실무 시나리오: 집계 함수와 에러 처리

판매 대시보드용 "지역별 매출 요약"을 작성합니다. COUNT/SUM/AVG/MIN/MAX를 한 번에 묶고, try-catch와 로깅까지 추가합니다.

const cds = require('@sap/cds')
const LOG = cds.log('sales-analytics')

module.exports = cds.service.impl(async function () {
  const { Orders } = this.entities

  this.on('regionSummary', async (req) => {
    const { fromDate, toDate } = req.data
    try {
      const rows = await SELECT.from(Orders)
        .columns`
          region,
          COUNT(*)        as orderCount,
          SUM(amount)     as totalAmount,
          AVG(amount)     as avgAmount,
          MIN(amount)     as minAmount,
          MAX(amount)     as maxAmount
        `
        .where`orderDate between ${fromDate} and ${toDate}`
        .groupBy`region`
        .having`SUM(amount) > 1000`
        .orderBy`totalAmount desc`

      LOG.info(`region summary: ${rows.length} groups`)
      return rows
    } catch (e) {
      LOG.error('regionSummary failed', e)
      req.error(500, 'Failed to compute region summary')
    }
  })
})

주문 상태(status)와 지역(region) 두 차원으로 묶어 매출 매트릭스를 만들 수도 있습니다.

// 다차원 그룹핑 — 지역 x 상태
const matrix = await SELECT.from(Orders)
  .columns`region, status, COUNT(*) as cnt, SUM(amount) as total`
  .groupBy`region, status`
  .orderBy`region, status`

Association을 활용한 집계도 자연스럽게 작성됩니다. 고객별 평균 주문액을 구할 때 customer ID로 그룹핑합니다.

const customerStats = await SELECT.from(Orders)
  .columns`customer_ID, COUNT(*) as cnt, AVG(amount) as avg`
  .groupBy`customer_ID`
  .having`COUNT(*) > 3`     // VIP 고객만

3단계 — 프로덕션: HAVING + 서브쿼리 + 최적화

WHERE vs HAVING — WHERE는 그룹핑 전에 행 단위로 필터링하고, HAVING은 그룹핑 후에 집계 결과로 필터링합니다. "유효 주문만 추리고, 매출 1000 초과 지역만 남기기"를 결합해봅니다.

const hotRegions = await SELECT.from(Orders)
  .columns`region, SUM(amount) as total`
  .where`status = 'CONFIRMED'`     // 행 필터 (그룹핑 전)
  .groupBy`region`
  .having`SUM(amount) > 1000`      // 그룹 필터 (그룹핑 후)
  .orderBy`total desc`

이제 한 단계 더 — 서브쿼리 패턴입니다. "주문 5건 이상인 우수 고객의 모든 주문 상세"를 한 번에 가져옵니다.

// 1) 우수 고객 ID만 뽑는 서브쿼리
const TopCustomers = SELECT`customer_ID`.from(Orders)
  .groupBy`customer_ID`
  .having`COUNT(*) >= 5`

// 2) 메인 쿼리에서 IN으로 결합
const detail = await SELECT.from(Orders)
  .where`customer_ID in ${TopCustomers}`
  .orderBy`customer_ID, orderDate desc`

TopCustomers는 즉시 실행되지 않고 CQN(JSON 표현)으로 메인 쿼리에 인라인되므로, DB에서 한 번의 SQL로 처리됩니다. 이게 cds.ql의 강력한 지점입니다.

최적화 체크리스트

• 인덱스: groupBy 대상 컬럼(region, status, customer_ID)에 인덱스를 거는 것이 일반적으로 권장됩니다. CDS에서는 @cds.persistence.index 또는 마이그레이션 SQL로 부여합니다.
• $apply: OData V4의 $apply=groupby((region),aggregate(amount with sum as total))도 cds.ql과 동일한 SQL로 변환되므로, 클라이언트에서 직접 OData 집계 쿼리를 보내도 됩니다.
• N+1 회피: 그룹별 상세를 다시 루프 돌며 SELECT하지 말고, 위 서브쿼리 패턴이나 expand를 사용합니다.
• 테스트: cds.test()로 in-memory SQLite에서 집계 결과를 단위 테스트하고, 운영 HANA에서는 EXPLAIN으로 실행 계획을 확인합니다.

// 간단 테스트 (Jest + cds.test)
const cds = require('@sap/cds/lib')
const { expect } = cds.test(__dirname + '/..')

test('regionSummary returns groups sorted by total desc', async () => {
  const srv = await cds.connect.to('SalesService')
  const rows = await srv.regionSummary({
    fromDate: '2026-01-01', toDate: '2026-12-31'
  })
  expect(rows[0].totalAmount).toBeGreaterThanOrEqual(rows[1]?.totalAmount ?? 0)
})

흔한 실수 / 트러블슈팅 FAQ

Q1. SELECT amount, COUNT(*) ... GROUP BY region이 에러납니다.
A. 표준 SQL 규칙상 GROUP BY에 포함되지 않은 비집계 컬럼은 SELECT에 올릴 수 없습니다. SQLite는 관대하지만 HANA/PostgreSQL은 거부합니다. amount를 SUM(amount)로 감싸거나 GROUP BY 절에 추가하세요.

Q2. HAVING에서 where 메서드를 또 쓰면 어떻게 되나요?
A. .where()는 그룹핑 전, .having()은 그룹핑 후입니다. 둘 다 같은 표현식 형식(tagged template, alternating string/value, query-by-example)을 받지만 적용 시점이 다릅니다. 집계 함수는 HAVING에만 들어갈 수 있습니다.

Q3. 결과의 SUM(amount) 컬럼명이 이상하게 나옵니다.
A. 별칭을 명시하지 않으면 DB마다 다른 이름이 반환됩니다. 항상 SUM(amount) as total처럼 as로 별칭을 부여하세요. JS 객체 키로 안정적으로 접근할 수 있습니다.

Q4. Decimal 타입 합계가 문자열로 나옵니다.
A. CAP은 정밀도 보존을 위해 Decimal을 일반적으로 문자열로 직렬화합니다. Number()나 BigDecimal 라이브러리로 변환해 사용하세요.

Q5. 동적 GROUP BY 컬럼을 사용자 입력으로 받고 싶습니다.
A. tagged template은 SQL 인젝션을 방지하지만 컬럼명 자체에는 사용할 수 없습니다. 화이트리스트로 검증한 뒤 .groupBy(...allowedCols) 형태로 전달하는 패턴이 권장됩니다.

다음 단계 / 관련 주제

• OData $apply: 클라이언트에서 OData V4 집계를 직접 보내는 방식 — UI5 SmartChart, Analytical List Page와 잘 맞습니다.
• HANA 분석 뷰: 대용량 집계는 Calculation View로 위임하고 CAP에서 projection만 노출하는 패턴.
• Materialized aggregates: 자주 쓰이는 집계는 별도 테이블에 사전 계산해두는 ETL 전략.
• 이전 글 "cds.ql CQN 쿼리", "OData V4 쿼리 심화 — $filter, $expand, $orderby"와 함께 보면 쿼리 레이어 그림이 완성됩니다.

참고 자료

• CAP Node.js — cds.ql Reference
• CAP — CQL (Core Query Language) Specification
• CAP — CQN (Core Query Notation) JSON 표현
• CAP — Databases Guide (HANA / SQLite)
• CAP — OData $apply Aggregation
• help.sap.com — SAP Cloud Application Programming Model
• help.sap.com — HANA SQL GROUP BY Clause
• help.sap.com — HANA SQL Aggregate Functions

핵심 한 줄

집계는 DB에게 시키고, cds.ql에는 .columns SUM(...)·.groupBy·.having 세 줄만 적으면 끝.