그룹의 첫 번째 값과 마지막 값 선택

그룹의 첫 번째 값과 마지막 값 선택

일별 주식 시세(오픈, 하이, 로우, 클로즈 및 볼륨)로 구성된 MySql 테이블을 가지고 있으며, 이를 주간 데이터로 즉시 변환하려고 합니다.지금까지, 저는 다음과 같은 함수를 가지고 있는데, 이 함수는 고점, 저점, 용적에 적용됩니다.

SELECT MIN(_low), MAX(_high), AVG(_volume),
CONCAT(YEAR(_date), "-", WEEK(_date)) AS myweek
FROM mystockdata
GROUP BY myweek
ORDER BY _date;

위 쿼리에서 _open의 첫 번째 인스턴스를 선택해야 합니다.예를 들어 월요일(특정 주)에 공휴일이 있고 화요일에 주식 시장이 열린 경우, 화요일에 공개 가치를 해당 주로 분류된 화요일에서 선택해야 합니다.마찬가지로, 폐점값은 그 주부터 마지막으로 닫혀 있어야 합니다.

MySql에서 FIRST()와 LAST()와 같은 것을 선택하여 중첩 선택 쿼리를 사용하는 것이 아니라 위의 내용을 하나의 SELECT 내에서 마무리할 수 있습니까?

스키마에 대한 아이디어를 얻기 위한 테이블 작성문은 다음과 같습니다.

delimiter $$
CREATE TABLE `mystockdata` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `symbol_id` int(11) NOT NULL,
  `_open` decimal(11,2) NOT NULL,
  `_high` decimal(11,2) NOT NULL,
  `_low` decimal(11,2) NOT NULL,
  `_close` decimal(11,2) NOT NULL,
  `_volume` bigint(20) NOT NULL,
  `add_date` date NOT NULL,
  PRIMARY KEY (`id`),
  KEY `Symbol_Id` (`symbol_id`,`add_date`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8$$

업데이트: Null은 없습니다. 휴일/주말이 있는 곳에는 해당 날짜에 대한 기록이 없습니다.

MySQL 8을 사용하는 경우 현재 사용 가능한 창 기능 FIRST_VALUE() 및/또는 LAST_VALUE()를 사용하는 것이 좋습니다.루카스 에더 씨의 답변을 한 번 봐주세요.

그러나 이전 버전의 MySQL을 사용하는 경우 이러한 기능은 지원되지 않습니다.다음과 같은 해결 방법을 사용하여 시뮬레이션을 수행해야 합니다. 예를 들어 다음과 같은 집합을 만드는 집계 문자열 함수 GROUP_CONCAT()를 사용할 수 있습니다._open그리고._close순으로 매겨진 주간의 값_date위해서_open그리고 그때까지_date desc위해서_close그리고 상기 의 첫 원소를 을 특징으로 , 의 를 합니다 합니다 를 .

select
  min(_low),
  max(_high),
  avg(_volume),
  concat(year(_date), "-", lpad(week(_date), 2, '0')) AS myweek,
  substring_index(group_concat(cast(_open as CHAR) order by _date), ',', 1 ) as first_open,
  substring_index(group_concat(cast(_close as CHAR) order by _date desc), ',', 1 ) as last_close
from
  mystockdata
group by
  myweek
order by
  myweek
;

은 은 를 할 입니다 과 입니다 할 은 를 과 LIMIT 1SELECT조항:

select
  min(_low),
  max(_high),
  avg(_volume),
  concat(year(_date), "-", lpad(week(_date), 2, '0')) AS myweek,
  (
    select _open
    from mystockdata m
    where concat(year(_date), "-", lpad(week(_date), 2, '0'))=myweek
    order by _date
    LIMIT 1
  ) as first_open,
  (
    select _close
    from mystockdata m
    where concat(year(_date), "-", lpad(week(_date), 2, '0'))=myweek
    order by _date desc
    LIMIT 1
  ) as last_close
from
  mystockdata
group by
  myweek
order by
  myweek
;

LPAD() 문자열 기능을 추가했습니다.myweek 두 길게 , 가 정확하지 , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , . 그렇지 않으면 주 순서가 정확하지 않습니다.

group_concat()과 함께 substring_index를 사용할 때도 주의해야 합니다. 그룹화된 문자열 중 하나에 쉼표가 포함되어 있으면 함수가 예상한 결과를 반환하지 않을 수 있습니다.

MySQL 8부터 작업에 이상적으로 윈도우 함수를 사용합니다.

WITH 
  t1 AS (
    SELECT _low, _high, _volume, CONCAT(YEAR(_date), "-", WEEK(_date)) AS myweek
    FROM mystockdata
  ),
  t2 AS (
    SELECT 
      t1.*, 
      FIRST_VALUE(_open) OVER (PARTITION BY myweek ORDER BY _date) AS first_open,
      FIRST_VALUE(_close) OVER (PARTITION BY myweek ORDER BY _date DESC) AS last_close
    FROM t1
  )
SELECT MIN(_low), MAX(_high), AVG(_volume), myweek, MIN(first_open), MAX(last_close)
FROM t2
GROUP BY myweek
ORDER BY myweek;

당신은 아마 다음과 같이 해야 할 것입니다.COALESCE첫 번째 값을 얻는 함수.데이터가날 및 에는 Δ Δ Δ Δ Δ Δ Δ Δ Δ Δ Δ Δ Δ Δ Δ Δ Δ Δ Δ Δ Δ Δ Δ Δ Δ Δ Δ Δ Δ Δ ΔΔ Δ Δ Δ Δ Δ Δ ΔΔ Δ_open데이터가 없는 그 날들에

용도는 다음과 같습니다.

SELECT MIN(_low), MAX(_high), AVG(_volume), COALESCE(_open)
CONCAT(YEAR(_date), "-", WEEK(_date)) AS myweek
FROM mystockdata
GROUP BY myweek
ORDER BY _date;

마지막() 값에 대해서는 꽤나 해킹적인 해결책만 생각해 낼 수 있습니다.GROUP_CONCAT목록에서 마지막 값을 얻기 위해 문자열을 조작합니다.그래서 아마도 이런 것들이 있을 것입니다.

SELECT MIN(_low), MAX(_high), AVG(_volume), COALESCE(_open), SUBSTRING_INDEX(GROUP_CONCAT(_close), ',', -1)
CONCAT(YEAR(_date), "-", WEEK(_date)) AS myweek
FROM mystockdata
GROUP BY myweek
ORDER BY _date;

참고로 사용할 수 있습니다.GROUP_CONCAT일관된 조회를 원하는 경우 병합 대신 첫 번째 항목에 대한 접근 방식

SELECT MIN(_low), MAX(_high), AVG(_volume), SUBSTRING_INDEX(GROUP_CONCAT(_open), ',', 1), SUBSTRING_INDEX(GROUP_CONCAT(_close), ',', -1)
CONCAT(YEAR(_date), "-", WEEK(_date)) AS myweek
FROM mystockdata
GROUP BY myweek
ORDER BY _date;

위해서GROUP_CONCAT제대로 작동하려면 값이 없는 날짜가 null in인지 확인해야 합니다._open그리고._close들녘

기본적으로, 당신이 해야 할 일은:

1. PRODUCT ID별 그룹
2. 각 그룹 내에서 위치별 순서 지정
3. 위치별로 주문한 동일한 상품의 첫 번째 가격을 선택합니다.

이들을 종합하면 다음 쿼리를 사용할 수 있습니다.

SELECT PRODUCTID, 
   SUBSTRING_INDEX(GROUP_CONCAT(CAST(LOCATION AS CHAR) ORDER BY LOCATION DESC), ',', 1) AS LOCATION,
   SUBSTRING_INDEX(GROUP_CONCAT(CAST(PRICE AS CHAR) ORDER BY LOCATION DESC), ',', 1) AS PRICE
FROM ProductLocation
GROUP BY PRODUCTID;

MySQL에는 GROUP BY에 대한 FIRST() 및 LAST() 집합 함수가 없지만 이러한 FIRST() 및 LAST() 집합 함수는 GROUP_CONCAT() 및 SUBSRING_INDEX() 함수를 사용하여 시뮬레이션할 수 있습니다.

언급URL : https://stackoverflow.com/questions/13957082/selecting-first-and-last-values-in-a-group