programing

MySQL에서 피벗 테이블 출력을 반환하려면 어떻게 해야 합니까?

lovejava 2023. 9. 26. 19:13

MySQL에서 피벗 테이블 출력을 반환하려면 어떻게 해야 합니까?

다음과 같은 MySQL 테이블이 있는 경우:

회사_이름 작업 페이지 수-------------------------------A사 PRINT 3A사 PRINT 2A사 PRINT 3B사 이메일B사 PRINT 2B사 PRINT 2B사 PRINT 1A사 PRINT 3

MySQL 쿼리를 실행하여 다음과 같은 출력을 얻을 수 있습니까?

company_name 이메일 프린트 1페이지 PRINT 2페이지 PRINT 2페이지 PRINT 3페이지-------------------------------------------------------------A사 0 0 13B사 1 1 1 2 0

은.pagecount열각다에 의 열을 할 수 있습니다.action/pagecount을 이룬 s 당 수company_name하지 않지만 을 제안했습니다 이게 피벗 테이블이라고 불리는지는 모르겠지만 누군가가 제안한 건가요?

이것은 기본적으로 피벗 테이블입니다.

이를 달성하는 방법에 대한 좋은 튜토리얼은 여기 http://www.artfulsoftware.com/infotree/qrytip.php?id=78 에서 확인할 수 있습니다.

저는 이 게시물을 읽고 이 해결책을 당신의 필요에 맞게 조정할 것을 권합니다.

갱신하다

위의 링크가 현재 더 이상 사용할 수 없게 된 후, 저는 여기에서 mysql 피벗 답변을 검색하는 여러분 모두에게 추가적인 정보를 제공할 의무가 있다고 생각합니다.그것은 정말로 방대한 양의 정보를 가지고 있었고, 나는 거기서 나오는 모든 것을 여기에 넣지는 않을 것입니다(그들의 방대한 지식을 복사하고 싶지 않기 때문에 더 많이), 하지만 나는 우선 질문을 한 페쿠의 예를 들어 일반적으로 피벗 테이블을 sql 방식으로 다루는 방법에 대해 몇 가지 조언을 줄 것입니다.

링크가 곧 돌아올 수도 있으니 계속 지켜보도록 하겠습니다.

스프레드시트 방식...

많은 사람들이 MSExcel, OpenOffice 또는 기타 스프레드시트 도구와 같은 도구를 이 목적으로 사용합니다.이것은 유효한 솔루션입니다. 데이터를 복사하고 GUI가 제공하는 툴을 사용하여 해결하면 됩니다.

하지만... 이 문제는 문제가 아니었고, 데이터를 스프레드시트에 저장하는 방법, 문제가 있는 확장 등과 같은 몇 가지 단점을 초래할 수도 있습니다.

SQL 방식...

그의 테이블이 이렇게 보이는 걸 보면 다음과 같습니다.

CREATE TABLE `test_pivot` (
  `pid` bigint(20) NOT NULL AUTO_INCREMENT,
  `company_name` varchar(32) DEFAULT NULL,
  `action` varchar(16) DEFAULT NULL,
  `pagecount` bigint(20) DEFAULT NULL,
  PRIMARY KEY (`pid`)
) ENGINE=MyISAM;

이제 그/그녀가 원하는 테이블을 살펴봅니다.

company_name    EMAIL   PRINT 1 pages   PRINT 2 pages   PRINT 3 pages
-------------------------------------------------------------
CompanyA        0       0               1               3
CompanyB        1       1               2               0

()EMAIL,PRINT x pages을 닮았습니다 조건을 닮았습니다.다별로 됩니다.company_name.

조건을 설정하기 위해 오히려 -statement를 사용하라고 외칩니다.어떤 것으로 그룹을 나누려면...GROUP BY.

이 피벗을 제공하는 기본 SQL은 다음과 같습니다.

SELECT  P.`company_name`,
    COUNT(
        CASE 
            WHEN P.`action`='EMAIL' 
            THEN 1 
            ELSE NULL 
        END
    ) AS 'EMAIL',
    COUNT(
        CASE 
            WHEN P.`action`='PRINT' AND P.`pagecount` = '1' 
            THEN P.`pagecount` 
            ELSE NULL 
        END
    ) AS 'PRINT 1 pages',
    COUNT(
        CASE 
            WHEN P.`action`='PRINT' AND P.`pagecount` = '2' 
            THEN P.`pagecount` 
            ELSE NULL 
        END
    ) AS 'PRINT 2 pages',
    COUNT(
        CASE 
            WHEN P.`action`='PRINT' AND P.`pagecount` = '3' 
            THEN P.`pagecount` 
            ELSE NULL 
        END
    ) AS 'PRINT 3 pages'
FROM    test_pivot P
GROUP BY P.`company_name`;

이것은 원하는 결과를 매우 빠르게 제공할 것입니다.이 접근 방식의 가장 큰 단점은 피벗 테이블에 행이 많을수록 SQL 문에 더 많은 조건을 정의해야 한다는 것입니다.

이것도 처리할 수 있기 때문에 사람들은 준비된 진술, 일상, 카운터 등을 사용하는 경향이 있습니다.

이 항목에 대한 몇 가지 추가 링크:

제 솔루션은 아무런 중심축 없이 T-SQL에 있습니다.

SELECT
    CompanyName,  
    SUM(CASE WHEN (action='EMAIL') THEN 1 ELSE 0 END) AS Email,
    SUM(CASE WHEN (action='PRINT' AND pagecount=1) THEN 1 ELSE 0 END) AS Print1Pages,
    SUM(CASE WHEN (action='PRINT' AND pagecount=2) THEN 1 ELSE 0 END) AS Print2Pages,
    SUM(CASE WHEN (action='PRINT' AND pagecount=3) THEN 1 ELSE 0 END) AS Print3Pages
FROM 
    Company
GROUP BY 
    CompanyName

MySQL 의에 수 .SUM()함수를 사용하면 부울(Boolean)로 평가됩니다. 따라서 사용하지 않고 기준에 따라 카운트를 가질 수 있습니다.IF/CASE

SELECT
    company_name,  
    SUM(action = 'EMAIL')AS Email,
    SUM(action = 'PRINT' AND pagecount = 1)AS Print1Pages,
    SUM(action = 'PRINT' AND pagecount = 2)AS Print2Pages,
    SUM(action = 'PRINT' AND pagecount = 3)AS Print3Pages
FROM t
GROUP BY company_name

DEMO

동적 피벗의 경우 다음을 사용합니다.GROUP_CONCAT와 함께CONCAT. GROUP_CONCAT 함수는 그룹의 문자열을 다양한 옵션으로 하나의 문자열로 연결합니다.

SET @sql = NULL;
SELECT
    GROUP_CONCAT(DISTINCT
    CONCAT(
      'SUM(CASE WHEN action = "',
      action,'"  AND ', 
           (CASE WHEN pagecount IS NOT NULL 
           THEN CONCAT("pagecount = ",pagecount) 
           ELSE pagecount IS NULL END),
      ' THEN 1 ELSE 0 end) AS ',
      action, IFNULL(pagecount,'')
      
    )
  )
INTO @sql
FROM
  t;

SET @sql = CONCAT('SELECT company_name, ', @sql, ' 
                  FROM t 
                   GROUP BY company_name');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

데모 여기

부울 논리를 사용하는 stard-SQL 버전:

SELECT company_name
     , COUNT(action = 'EMAIL' OR NULL) AS "Email"
     , COUNT(action = 'PRINT' AND pagecount = 1 OR NULL) AS "Print 1 pages"
     , COUNT(action = 'PRINT' AND pagecount = 2 OR NULL) AS "Print 2 pages"
     , COUNT(action = 'PRINT' AND pagecount = 3 OR NULL) AS "Print 3 pages"
FROM   tbl
GROUP  BY company_name;

db<>여기서 fid
오래된 sqlfidle

어떻게?

TRUE OR NULL이 껑충 껑충 껑충 껑충 껑충 뛰다TRUE.
FALSE OR NULL이 껑충 껑충 껑충 껑충 껑충 뛰다NULL.
NULL OR NULL이 껑충 껑충 껑충 껑충 껑충 뛰다NULL.
null이 아닌 값만 계산합니다.Voila.

정답은 다음과 같습니다.

select table_record_id,
group_concat(if(value_name='note', value_text, NULL)) as note
,group_concat(if(value_name='hire_date', value_text, NULL)) as hire_date
,group_concat(if(value_name='termination_date', value_text, NULL)) as termination_date
,group_concat(if(value_name='department', value_text, NULL)) as department
,group_concat(if(value_name='reporting_to', value_text, NULL)) as reporting_to
,group_concat(if(value_name='shift_start_time', value_text, NULL)) as shift_start_time
,group_concat(if(value_name='shift_end_time', value_text, NULL)) as shift_end_time
from other_value
where table_name = 'employee'
and is_active = 'y'
and is_deleted = 'n'
GROUP BY table_record_id

MySQL 피벗 테이블 생성기라는 도구가 있는데 나중에 Excell(원하는 경우)로 내보낼 수 있는 웹 기반 피벗 테이블을 만드는 데 도움이 됩니다.데이터가 단일 테이블에 있거나 여러 테이블에 있는 경우 작동할 수 있습니다.

의 값 및 , , () 의의 됩니다.MySQL Pivot Table

이 툴의 홈페이지는 https://mysqlreports.com/mysql-reporting-tools/mysql-pivot-table/ 입니다.

select t3.name, sum(t3.prod_A) as Prod_A, sum(t3.prod_B) as Prod_B, sum(t3.prod_C) as    Prod_C, sum(t3.prod_D) as Prod_D, sum(t3.prod_E) as Prod_E  
from
(select t2.name as name, 
case when t2.prodid = 1 then t2.counts
else 0 end  prod_A, 

case when t2.prodid = 2 then t2.counts
else 0 end prod_B,

case when t2.prodid = 3 then t2.counts
else 0 end prod_C,

case when t2.prodid = 4 then t2.counts
else 0 end prod_D, 

case when t2.prodid = "5" then t2.counts
else 0 end prod_E

from 
(SELECT partners.name as name, sales.products_id as prodid, count(products.name) as counts
FROM test.sales left outer join test.partners on sales.partners_id = partners.id
left outer join test.products on sales.products_id = products.id 
where sales.partners_id = partners.id and sales.products_id = products.id group by partners.name, prodid) t2) t3

group by t3.name ;

MySQL 데이터베이스에 대한 집합 내에서 문 사용이 중복되는 것을 결합하고, 다음 코드 블록과 같이 결과 집합에 대한 적절한 열 제목을 얻는 것과 함께 필요한 쿼리 생성을 동적으로 고려하는 것이 한 가지 방법입니다.

SET @sql = NULL;

SELECT GROUP_CONCAT(
             CONCAT('SUM( `action` = ''', action, '''',pc0,' ) AS ',action,pc1)
       )
  INTO @sql
  FROM 
  ( 
   SELECT DISTINCT `action`, 
          IF(`pagecount` IS NULL,'',CONCAT('page',`pagecount`)) AS pc1,
          IF(`pagecount` IS NULL,'',CONCAT(' AND `pagecount` = ', pagecount, '')) AS pc0
     FROM `tab` 
    ORDER BY CONCAT(action,pc0) 
  ) t;

SET @sql = CONCAT('SELECT company_name,',@sql,' FROM `tab` GROUP BY company_name'); 
SELECT @sql; 

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

Demo

SELECT company_name, SUM(CASE WHEN ACTION = 'Email' THEN 1 ELSE 0 END) AS "Email",
SUM(CASE WHEN ACTION = 'Print' AND pagecount = 1 THEN 1 ELSE 0 END) AS "print 1 PAGE",
SUM(CASE WHEN ACTION = 'Print' AND pagecount = 2 THEN 1 ELSE 0 END) AS "print 2 PAGE",
SUM(CASE WHEN ACTION = 'Print' AND pagecount = 3 THEN 1 ELSE 0 END) AS "print 2 PAGE"
FROM test1 GROUP BY company_name;

언급URL : https://stackoverflow.com/questions/52519455/mysql-select-adding-duplicite-rows-data-to-new-columns

'programing' 카테고리의 다른 글

IN 절의 MySQL 여러 열  (0) 2023.09.26
C# 컴파일은 헤더 파일이 필요한 상황에서 어떻게 해결됩니까?  (0) 2023.09.26
화면이 1024px보다 좁을 경우 ADIV 숨기기  (0) 2023.09.26
GUID/UUID 데이터베이스 키의 장단점  (0) 2023.09.26
몽고에서 Spring Boot을 사용하여 생 JSON을 저장하는 방법  (0) 2023.09.26

'programing'의 다른글

  • 현재글MySQL에서 피벗 테이블 출력을 반환하려면 어떻게 해야 합니까?

관련글

댓글

티스토리툴바