programing

SQL에서 합산 및 그룹화에 따른 결과 차이

newstyles 2023. 9. 16. 08:43

SQL에서 합산 및 그룹화에 따른 결과 차이

작은 홈 프로젝트를 수행하면서 부분적으로 동일한 답변을 산출해야 하는 두 개의 쿼리에 의해 출력되는 값의 차이를 발견했습니다.

제가 사용한 전기 에너지를 계산하는 프로젝트입니다.아래 쿼리를 영어(원본 네덜란드어)로 번역하였습니다.

select month(measured.Date) as Month, sum(measured.used_kwh), sum(measured.used_E) from
(select DATE_FORMAT(highRate.time,'%Y-%m-%d') as Date,
max(highRate.Value)-min(highRate.Value) + max(LowRate.Value)-min(LowRate.Value) as used_kwh,
(max(highRate.Value)-min(highRate.Value))*0.2096 + (max(LowRate.Value)-min(LowRate.Value))*0.1943 as used_E
from Item8 as highRate
left join Item7 as LowRate
on highRate.Time = LowRate.Time
group by Date) as measured
group by Month;

수율:

select MONTH(highRate.time) as Month, 
max(highRate.Value)-min(highRate.Value) + max(LowRate.Value)-min(LowRate.Value) as used_kwh,
(max(highRate.Value)-min(highRate.Value))*0.2096 + (max(LowRate.Value)-min(LowRate.Value))*0.1943 as used_E
from Item8 as highRate
left join Item7 as LowRate
on highRate.Time = LowRate.Time
group by Month;

수율:

여기서 무슨 일이 일어나는지 알아내기 위해 오후에 대부분 시도했지만 아직까지 결과가 없습니다.두 번째 열(sum(gemeten.verbruik_kwh) / verbruik_kwh)에서는 항상 0.15 정도의 차이가 납니다.

위의 쿼리는 최종 버전만큼 필요하지 않고 구성되어 있지 않습니다. 이 특정 부분을 해당 부분이 속한 더 큰 쿼리에서 복사하고 독립 실행형으로 작동하도록 변경하기 때문입니다.

아래 스크린샷에는 차이가 다시 나타나지만, 사용자에게 보여주기를 바라는 방식으로.표에서 "Gemeten verbruik(€)" 및 "Gemeten verbruik(kWh)" 값이 모두 같아야 합니다.

쿼리가 완전히 같지는 않습니다.첫 번째 항목은 적절한 들여쓰기로 볼 수 있는 두 개의 계층 집합을 실행합니다. 첫 번째 항목은 날짜 수준이고 두 번째 항목은 월 수준입니다.두 번째 쿼리는 월 수준에서 하나의 집계만 실행합니다.

데이터에서 날짜로 그룹화된 max/min 값이 월로 그룹화된 max/min 값과 약간 다를 수 있습니다.동일한 월 내에 여러 개의 날짜 레코드가 있을 수 있습니다.

첫번째 쿼리

SELECT month(measured.Date) AS MONTH,
       sum(measured.used_kwh),
       sum(measured.used_E)
FROM
  (SELECT DATE_FORMAT(highRate.time,'%Y-%m-%d') AS Date,

          max(highRate.Value)-min(highRate.Value) +
          max(LowRate.Value)-min(LowRate.Value) AS used_kwh,

          (max(highRate.Value)-min(highRate.Value))*0.2096 + 
          (max(LowRate.Value)-min(LowRate.Value))*0.1943 AS used_E

   FROM Item8 AS highRate
   LEFT JOIN Item7 AS LowRate ON highRate.Time = LowRate.Time
   GROUP BY Date) AS measured

GROUP BY MONTH;

두번째 쿼리

SELECT MONTH(highRate.time) AS MONTH,

       max(highRate.Value)-min(highRate.Value) + 
       max(LowRate.Value)-min(LowRate.Value) AS used_kwh,

       (max(highRate.Value)-min(highRate.Value))*0.2096 + 
       (max(LowRate.Value)-min(LowRate.Value))*0.1943 AS used_E

FROM Item8 AS highRate
LEFT JOIN Item7 AS LowRate ON highRate.Time = LowRate.Time

GROUP BY MONTH;

진정으로 동등한 중첩 쿼리는 외부가 중복되는 월 수준에서 집계되며 집계 함수도 다음으로 대체할 수 있습니다.Avg(),Min(),Max():

SELECT month(measured.Date) AS MONTH,
       sum(measured.used_kwh),
       sum(measured.used_E)
FROM
      (SELECT month(DATE_FORMAT(highRate.time,'%Y-%m-%d')) AS Month,

              max(highRate.Value)-min(highRate.Value) +
              max(LowRate.Value)-min(LowRate.Value) AS used_kwh,

              (max(highRate.Value)-min(highRate.Value))*0.2096 + 
              (max(LowRate.Value)-min(LowRate.Value))*0.1943 AS used_E

       FROM Item8 AS highRate
       LEFT JOIN Item7 AS LowRate ON highRate.Time = LowRate.Time
       GROUP BY Month) AS measured

GROUP BY MONTH;

@매트 베일리:첫 번째 방법을 고수하면 다음과 같은 결과를 얻을 수 있습니다.

increases: NULL; decreases: 18323.261840820312

두 번째 "눈덩이" 방법은 다음과 같은 결과를 낳았습니다.

Date    HighRateMinValue    HighRateMaxValue    LowRateMinValue LowRateMaxValue
2017-11-16  1358.1080322265625  1362.0860595703125  1252.5179443359375  1252.7440185546875
2017-11-17  1362.0860595703125  1370.177978515625   1252.761962890625   1254.592041015625
2017-11-18  1370.177978515625   1370.177978515625   1254.6280517578125  1262.7679443359375
2017-11-19  1370.177978515625   1370.177978515625   1262.7840576171875  1272.72900390625
2017-11-20  1370.177978515625   1375.876953125  1272.7469482421875  1274.7969970703125
2017-11-21  1375.876953125  1383.2359619140625  1274.81298828125    1277.541015625

이거 다 괜찮은 것 같은데요, 아니면 요점을 놓친 건가요?

데이터에 따라 몇 가지 가능한 답변이 있습니다.

충분한 양의 데이터가 있는 경우 부동 소수점 데이터 유형을 사용할 때 이 문제가 발생할 수 있습니다.긴 주제이지만 부동 소수점 데이터 유형이 십진수를 무한히 잘 표현하지 않으며 상당한 반올림 오류(흔히 타박상이라고 함)가 쉽게 발생할 수 있습니다.

https://docs.oracle.com/cd/E19957-01/806-3568/ncg_goldberg.html

첫 번째 예제에서 내부 쿼리의 결과는 무엇입니까?혹시 라운딩 오류의 원인을 알 수 있을까요?


그러나 데이터에 부조화가 있을 가능성이 높습니다.다음 행을 찾습니다.value이전 레코드와 비교하면 DOWN이 됩니다.

사용하는 것 같네요.MySQL지저분하긴 하지만 그런 줄을 확인할 수 있는...

SELECT
    SUM(CASE WHEN this.value < next.value THEN next.value - this.value END)  AS increases,
    SUM(CASE WHEN this.value > next.value THEN this.value - next.value END)  AS decreases
FROM
    Item8    AS this
INNER JOIN
    Item8    AS next
       ON next.time = (SELECT MIN(Item8.time) FROM Item8 WHERE Item8.time < this.time)

아니면, 이걸 시도해보고 그냥 눈을 맞추세요...

SELECT
    DATE_FORMAT(highRate.time,'%Y-%m-%d')   AS Date,
    MIN(highRate.Value)                     AS HighRateMinValue,
    MAX(highRate.Value)                     AS HighRateMaxValue,
    MIN(LowRate.Value)                      AS LowRateMinValue,
    MAX(LowRate.Value)                      AS LowRateMaxValue
FROM
    Item8   AS highRate
LEFT JOIN
    Item7   AS LowRate
        ON  highRate.Time = LowRate.Time
GROUP BY
    Date
ORDER BY
    Date

전날의 LowRateMaxValue보다 낮은 LowRateMinValue를 보게 된다면, 그것은 당신의 '문제'입니다.


이런 데이터가 있을 때 중요한 건...

   MAX( {1, 2, 3, 2, 3, 4} ) - MIN( {1, 2, 3, 2, 3, 4} )
=>
   4 - 1
=> 
   3

비교하면...

   [ MAX( {1, 2, 3} ) - MIN( {1, 2, 3} ) ]   +    [ MAX( {2, 3, 4} ) - MIN( {2, 3, 4} ) ]
=>
   [3 - 1] + [4 - 2]
=> 
   4


참고로 성능상의 이유로 집계 전에 JOIN... 보다는 집계 후 각 테이블의 결과를 비교하는 것이 더 나을 것입니다.

SELECT
    COALESCE(highRate.month, low_rate.month)                                            AS month,
    COALESCE(highRate.used_kwh, 0)          + COALESCE(lowRate.used_kwh, 0)             AS used_kwh,
    COALESCE(highRate.used_kwh, 0) * 0.2096 + COALESCE(lowRate.used_kwh, 0) * 0.1943    AS used_E
FROM
(
    SELECT
        DATE_FORMAT(Item8.time,'%Y-%m-01')    AS month,
        MAX(Item8.value) - MIN(Item8.value)   AS used_kwh
    FROM
        Item8
    GROUP BY
        day
)
    AS highRate
FULL OUTER JOIN
(
    SELECT
        DATE_FORMAT(Item7.time,'%Y-%m-01')    AS month,
        MAX(Item7.value) - MIN(Item7.value)   AS used_kwh
    FROM
        Item7
    GROUP BY
        day
)
    AS lowRate
        ON lowRate.month = highRate.month

이를 통해 쿼리 플래너는 각 테이블의 MIN 및 MAX 값(또는 테이블의 행 범위)을 훨씬 더 신속하게 식별할 수 있으며, 결합해야 하는 행의 수를 크게 줄일 수 있습니다.

이렇게 하면 LowRate에 HighRate가 아닌 행이 있는 경우와 동시에 여러 개의 항목이 있는 경우도 보호됩니다.

편집:

오늘 먼저 집계하고, 다음 달에 집계하는 집계 후 조인 버전입니다.join 버전입니다.

SELECT
    MONTH(COALESCE(highRate.day, low_rate.day))                                                   AS month,
    COALESCE(SUM(highRate.used_kwh), 0)          + COALESCE(SUM(lowRate.used_kwh), 0)             AS used_kwh,
    COALESCE(SUM(highRate.used_kwh), 0) * 0.2096 + COALESCE(SUM(lowRate.used_kwh), 0) * 0.1943    AS used_E
FROM
(
    SELECT
        DATE_FORMAT(Item8.time,'%Y-%m-%d')    AS day,
        MAX(Item8.value) - MIN(Item8.value)   AS used_kwh
    FROM
        Item8
    GROUP BY
        day
)
    AS highRate
FULL OUTER JOIN
(
    SELECT
        DATE_FORMAT(Item7.time,'%Y-%m-%d')    AS day,
        MAX(Item7.value) - MIN(Item7.value)   AS used_kwh
    FROM
        Item7
    GROUP BY
        day
)
    AS lowRate
        ON lowRate.day = highRate.day 
GROUP BY
   month

편집:

더 짧은 접근 방식(더 짧은 접근 방식)JOIN모래를COALESCE전부).

SELECT
    month,

    SUM(high)                                 AS used_kwh_high,
                         SUM(low)             AS used_kwh_low,
    SUM(high)          + SUM(low)             AS used_kwh,

    SUM(high) * 0.2096                        AS used_E_high,
                         SUM(low) * 0.1943    AS used_E_low,
    SUM(high) * 0.2096 + SUM(low) * 0.1943    AS used_E
FROM
(
    SELECT DATE_FORMAT(time,'%Y-%m-01') AS month, MAX(value) - MIN(value) AS high, 0 AS low FROM Item8 GROUP BY month
    UNION ALL
    SELECT DATE_FORMAT(time,'%Y-%m-01') AS month, 0 AS high, MAX(value) - MIN(value) AS low FROM Item7 GROUP BY month
)
    combined_rates
GROUP BY
    month

그리고 Day and Month 집계 버전은...

SELECT
    DATE_FORMAT(day,'%Y-%m-01') AS month,

    SUM(high)                                 AS used_kwh_high,
                         SUM(low)             AS used_kwh_low,
    SUM(high)          + SUM(low)             AS used_kwh,

    SUM(high) * 0.2096                        AS used_E_high,
                         SUM(low) * 0.1943    AS used_E_low,
    SUM(high) * 0.2096 + SUM(low) * 0.1943    AS used_E
FROM
(
    SELECT DATE_FORMAT(time,'%Y-%m-%d') AS day, MAX(value) - MIN(value) AS high, 0 AS low FROM Item8 GROUP BY day
    UNION ALL
    SELECT DATE_FORMAT(time,'%Y-%m-%d') AS day, 0 AS high, MAX(value) - MIN(value) AS low FROM Item7 GROUP BY day
)
    combined_rates
GROUP BY
    month

언급URL : https://stackoverflow.com/questions/47420116/difference-in-result-with-summing-and-grouping-in-sql

'programing' 카테고리의 다른 글

왜 일부 컴파일러는 실행 파일의 기본 이름으로 "a.out"을 사용합니까?  (0) 2023.09.16
package-lock.json의 취약한 npm 패키지를 package.json에 나열되지 않은 package-lock.json에서 수정하려면 어떻게 해야 합니까?  (0) 2023.09.16
MySQL에서 기본 키가 아닌 필드를 자동으로 증가시켜야 합니다.  (0) 2023.09.16
j효과 추가를 사용한 jQuery  (0) 2023.09.16
MS Visual C++에서 VLA(Variable Length Array) 활성화?  (0) 2023.09.16

'programing'의 다른글

  • 현재글SQL에서 합산 및 그룹화에 따른 결과 차이

관련글

댓글

티스토리툴바