Блог

Я хочу видеть статистику пользователя, поэтому я сделал запрос:

 SELECT l.partner AS Partner ,
       bu.meno||' '||decode(substr(bu.priezvisko, 1, 2), 'Sz',
                            substr(bu.priezvisko, 1, 2), 'Gy', 
                            substr(bu.priezvisko, 1, 2), 'Ny', 
                            substr(bu.priezvisko, 1, 2), 'Zs', 
                            substr(bu.priezvisko, 1, 2), 'Cs', 
                            substr(bu.priezvisko, 1, 2), 
                            substr(bu.priezvisko, 1, 1))
              ||'.' AS prod_man --hungarian names have 2letter (surname)
      , SUM(CASE
               WHEN o.pocet!=0 THEN 1
               ELSE 0
           END) AS obj_pocet --  counting items
      , SUM(CASE
               WHEN o.pocet=0 OR o.p_del o.p_del_dod>=o.pocet THEN 1
               ELSE 0
           END) AS nedod_pocet -- counting items2
      , ROUND(SUM(CASE
                     WHEN o.pocet=0 OR o.p_del o.p_del_dod>=o.pocet THEN 1
                     ELSE 0
                 END)/count(*), 3) * 100 AS "%" --percentage

FROM obj_odb_o o
JOIN obj_odb_l l ON o.rid_o=l.rid
JOIN sklad_karta sk ON sk.id=o.kod_id
JOIN bartex_users bu ON bu.id=sk.id.prod_man
WHERE l.partner in (325,
                    326)
GROUP BY l.partner
        , bu.meno||' '||decode(substr(bu.priezvisko, 1, 2), 'Sz',
                               substr(bu.priezvisko, 1, 2), 'Gy', 
                               substr(bu.priezvisko, 1, 2), 'Ny', 
                               substr(bu.priezvisko, 1, 2), 'Zs', 
                               substr(bu.priezvisko, 1, 2), 'Cs', 
                               substr(bu.priezvisko, 1, 2), 
                               substr(bu.priezvisko, 1, 1))
                 ||'.'
  

Это работает. Вот результат:

Но я хочу сделать сводку по месяцам (последние 6 месяцев)…

 WITH MONTHS AS 
(
SELECT ADD_MONTHS(TRUNC(SYSDATE,'MONTH'),-LEVEL 1) AS MONTH, 
 DECODE(LEVEL,1,'Akt_mesiac','minuly_mesiac'||(LEVEL-1)) AS MONTH_NAME FROM DUAL CONNECT BY LEVEL <=7)
SELECT 
    partner, 
    prod_man,
     '%',
    NVL(Akt_mesiac,0) AS Akt_mesiac,
    NVL(minuly_mesiac1,0) AS minuly_mesiac1,
    NVL(minuly_mesiac2,0) AS minuly_mesiac2,
    NVL(minuly_mesiac3,0) AS minuly_mesiac3,
    NVL(minuly_mesiac4,0) AS minuly_mesiac4,
    NVL(minuly_mesiac5,0) AS minuly_mesiac5,
    NVL(minuly_mesiac6,0) AS minuly_mesiac6
FROM (
SELECT 
     -- my query - HERE I HAVE PROBLEM HERE

 FROM MONTHS M
JOIN obj_odb_l l ON M.MONTH=TRUNC(l.datum_p,'MONTH')

) PIVOT 
( SUM(CNT) 
FOR MONTH_NAME IN 
    ('Akt_mesiac' AS Akt_mesiac,
    'minuly_mesiac1' AS minuly_mesiac1,
    'minuly_mesiac2' AS minuly_mesiac2,
    'minuly_mesiac3' AS minuly_mesiac3,
    'minuly_mesiac4' AS minuly_mesiac4,
    'minuly_mesiac5' AS minuly_mesiac5,
    'minuly_mesiac6' AS minuly_mesiac6)
  );
  

Таблица: obj_odb_l l -> столбец даты -> l.datum_p -> trunc(l.datum_p,’МЕСЯЦ’)

Как я могу создать сводную таблицу?

Рассмотрите возможность добавления выражения месяца, TRUNC(l.datum_p,'MONTH') , в приведенный выше сводный запрос. Затем запустите запрос как другой CTE в сводном запросе для JOIN источника данных pivot.

 WITH MONTHS AS ( 
    SELECT ADD_MONTHS(TRUNC(SYSDATE,'MONTH'),-LEVEL 1) AS MONTH
          , DECODE(LEVEL,1,'Akt_mesiac','minuly_mesiac'||(LEVEL-1)) AS MONTH_NAME 
    FROM DUAL CONNECT BY LEVEL <=7
)
    , AGG AS (
        -- SAME AGGREGATE QUERY WITH TRUNC(l.datum_p,'MONTH') ADDED TO SELECT AND GROUP BY
        -- POSSIBLY ADD WHERE CONDITION FOR LAST SIX MONTHS (IF DATA GOES BACK YEARS)
)

SELECT *    
FROM (
   SELECT AGG.partner
        , AGG.prod_man
        , AGG.obj_pocet
        , AGG.nedod_pocet
        , AGG.'%' AS PCT                               -- AVOID SPECIAL CHARS AS NAME
        , M.MONTH_NAME
   FROM MONTHS M
   INNER JOIN AGG 
     ON M.MONTH = AGG.MONTH                            -- NEW FIELD USED FOR JOIN
) 

PIVOT 
  ( SUM(PCT)                                           -- ONLY PIVOTS ONE NUM AT A TIME
      FOR MONTH_NAME IN 
        ('Akt_mesiac'     AS Akt_mesiac,
         'minuly_mesiac1' AS minuly_mesiac1,
         'minuly_mesiac2' AS minuly_mesiac2,
         'minuly_mesiac3' AS minuly_mesiac3,
         'minuly_mesiac4' AS minuly_mesiac4,
         'minuly_mesiac5' AS minuly_mesiac5,
         'minuly_mesiac6' AS minuly_mesiac6)
  );