MySQL과 다른 PostgreSQL GROUP BY?

MySQL과 다른 PostgreSQL GROUP BY?

---

Heroku를 사용하기 위해 MySQL 쿼리 중 일부를 PostgreSQL로 마이그레이션했습니다. 대부분의 쿼리는 정상적으로 작동하지만 group by를 사용할 때 비슷한 반복 오류가 계속 발생합니다.

오류 : "XYZ"열은 GROUP BY 절에 나타나거나 집계 함수에 사용되어야합니다.

누군가 내가 뭘 잘못하고 있는지 말해 줄 수 있습니까?

100 % 작동하는 MySQL :

SELECT `availables`.*
FROM `availables`
INNER JOIN `rooms` ON `rooms`.id = `availables`.room_id
WHERE (rooms.hotel_id = 5056 AND availables.bookdate BETWEEN '2009-11-22' AND '2009-11-24')
GROUP BY availables.bookdate
ORDER BY availables.updated_at

PostgreSQL 오류 :

ActiveRecord :: StatementInvalid : PGError : ERROR : "availables.id"열이 GROUP BY 절에 나타나거나 집계 함수에 사용되어야합니다.
SELECT "availables". * FROM "availables"INNER JOIN "rooms"ON "rooms". id = "availables".room_id WHERE (rooms.hotel_id = 5056 AND availables.bookdate BETWEEN E'2009-10-21 '및 E'2009-10-23') GROUP BY availables.bookdate ORDER BY availables.updated_at

SQL을 생성하는 Ruby 코드 :

expiration = Available.find(:all,
    :joins => [ :room ],
    :conditions => [ "rooms.hotel_id = ? AND availables.bookdate BETWEEN ? AND ?", hostel_id, date.to_s, (date+days-1).to_s ],
    :group => 'availables.bookdate',
    :order => 'availables.updated_at')  

예상 출력 (작동중인 MySQL 쿼리에서) :

+ ----- + ------- + ------- + ------------ + --------- + ---- ----------- + --------------- +
| 아이디 | 가격 | 명소 | 예약일 | room_id | created_at | updated_at |
+ ----- + ------- + ------- + ------------ + --------- + ---- ----------- + --------------- +
| 414 | 38.0 | 1 | 2009-11-22 | 1762 | 2009-11-20 ... | 2009-11-20 ... |
| 415 | 38.0 | 1 | 2009-11-23 | 1762 | 2009-11-20 ... | 2009-11-20 ... |
| 416 | 38.0 | 2 | 2009-11-24 | 1762 | 2009-11-20 ... | 2009-11-20 ... |
+ ----- + ------- + ------- + ------------ + --------- + ---- ----------- + --------------- +
3 줄 세트

---

MySQL의 완전히 표준을 준수하지 않는 GROUP BY것은 Postgres의 DISTINCT ON. 이걸 고려하세요:

MySQL :

SELECT a,b,c,d,e FROM table GROUP BY a

이것은 a(정말 알지 못하는) 값 당 1 행을 제공 합니다. 사실 MySQL은 해시 집계에 대해 알지 못하기 때문에 추측 할 수 있습니다. 따라서 아마도 정렬을 사용할 것입니다 ...하지만 정렬 만 할 것이므로 a행의 순서는 무작위가 될 수 있습니다. 정렬 대신 다중 열 색인을 사용하지 않는 한. 어쨌든 쿼리에 의해 지정되지 않았습니다.

Postgres :

SELECT DISTINCT ON (a) a,b,c,d,e FROM table ORDER BY a,b,c

이는의 값당 1 개의 행을 제공합니다 a.이 행은 ORDER BY쿼리에 지정된에 따라 정렬에서 첫 번째 행이됩니다 . 단순한.

여기서는 내가 계산하는 집계가 아닙니다. 그래서 GROUP BY실제로 말이되지 않습니다. DISTINCT ON훨씬 더 의미가 있습니다.

Rails는 MySQL과 결합되어 있으므로 Postgres에서 작동하지 않는 SQL을 생성한다는 사실에 놀랍지 않습니다.

---

PostgreSQL은 MySQL보다 SQL을 더 잘 준수합니다. 출력의 모든 필드 (집계 함수가있는 계산 된 필드 제외)는 GROUP BY 절에 있어야합니다.

---

MySQL의 GROUP BY는 집계 함수없이 사용할 수 있으며 (SQL 표준과 반대 임) 그룹의 첫 번째 행을 반환합니다 (어떤 기준에 따라 알 수 없음). 반면 PostgreSQL에는 집계 함수 (MAX, GROUP BY 절이 실행되는 열에 대한 SUM 등).

---

정답입니다.이 문제를 해결하는 방법은 : select를 사용하고 결과 개체를 장식하고 그룹별로 그룹화하려는 각 필드를 선택하는 것입니다.

불쾌한-그러나 그것은 당신이 당신의 그룹에 필드를 고정하지 않는다면 당신이 의미하는 바를 추측함으로써 MySQL이 작동하는 방식과 반대로 group by 가 작동하는 방식입니다.

---

내 기억이 맞다 경우, PostgreSQL을에 당신은 당신이 GROUP BY 절을 적용 테이블에서 가져 오는 모든 열을 추가 할 필요 에 GROUP BY 절을.

---

가장 예쁜 솔루션은 아니지만 모델의 모든 열을 출력하도록 그룹 매개 변수를 변경하면 PostgreSQL에서 작동합니다.

expiration = Available.find(:all,
:joins => [ :room ],
:conditions => [ "rooms.hotel_id = ? AND availables.bookdate BETWEEN ? AND ?", hostel_id, date.to_s, (date+days-1).to_s ],
:group => Available.column_names.collect{|col| "availables.#{col}"},
:order => 'availables.updated_at')

---

MySQL의 "Debuking GROUP BY Myths" http://dev.mysql.com/tech-resources/articles/debunking-group-by-myths.html 에 따르면 . SQL (2003 버전의 표준)은 쿼리의 SELECT 목록에서 참조 된 열이 GROUP BY 절에도 나타나도록 요구하지 않습니다.

---

조인 된 필드를 포함하여 모든 필드를 기준으로 정렬하는 방법을 찾는 다른 사용자는 postgresql에서 하위 쿼리를 사용합니다.

SELECT * FROM(
SELECT DISTINCT ON(availables.bookdate) `availables`.* 
FROM `availables` INNER JOIN `rooms` ON `rooms`.id = `availables`.room_id 
WHERE (rooms.hotel_id = 5056 
AND availables.bookdate BETWEEN '2009-11-22' AND '2009-11-24')
) AS distinct_selected
ORDER BY availables.updated_at

or arel:

subquery = SomeRecord.select("distinct on(xx.id) xx.*, jointable.order_field")
      .where("").joins(")
result = SomeRecord.select("*").from("(#{subquery.to_sql}) AS distinct_selected").order(" xx.order_field ASC, jointable.order_field ASC")

---

.uniq [1]이 문제를 해결할 것이라고 생각합니다.

[1] Available.select('...').uniq

Take a look at http://guides.rubyonrails.org/active_record_querying.html#selecting-specific-fields

참고URL : https://stackoverflow.com/questions/1769361/postgresql-group-by-different-from-mysql