Miravild

Существует таблица, содержащая статусы доставки сообщений:

CREATE TABLE SMS_MESSAGE_DLV_STATUS 
(sms_message_id NUMBER(20),
	dlv_status VARCHAR2(20),
	dlv_status_at TIMESTAMP
) TABLESPACE MIRAVILD_DATA;
 
COMMENT ON TABLE SMS_MESSAGE_DLV_STATUS IS 'Статус сообщения';
COMMENT ON COLUMN SMS_MESSAGE_DLV_STATUS.sms_message_id IS 'Идентификатор статуса';
COMMENT ON COLUMN SMS_MESSAGE_DLV_STATUS.dlv_status IS 'Статус доставки сообщения';
COMMENT ON COLUMN SMS_MESSAGE_DLV_STATUS.dlv_status_at IS 'Дата сообщения';

CREATE UNIQUE INDEX "U_IDX_SMS_MESSAGE_STATUS" ON SMS_MESSAGE_DLV_STATUS (sms_message_id) TABLESPACE MIRAVILD_INDEX;

ALTER TABLE SMS_MESSAGE_DLV_STATUS ADD CONSTRAINT "PK_SMS_MESSAGE_STATUS" PRIMARY KEY (sms_message_id)
  USING INDEX "U_IDX_SMS_MESSAGE_STATUS" ENABLE VALIDATE;

ALTER TABLE SMS_MESSAGE_DLV_STATUS MODIFY (dlv_status CONSTRAINT SMS_MESSAGE_STATUS_NN NOT NULL ENABLE);
ALTER TABLE SMS_MESSAGE_DLV_STATUS MODIFY (dlv_status_at CONSTRAINT SMS_MESSAGE_AT_NN NOT NULL ENABLE);
/

Нужно получить отчет о доставке с разбивкой по дням:

DATE         DLV_STATUS          COUNT

20.08.2015 DELIVERED                100000

20.08.2015 UNDELIVERED          10

20.08.2015 FAILED                         1

21.08.2015 DELIVERED                 150000

21.08.2015 UNDELIVERED           20

21.08.2015 FAILED                          2

22.08.2015 DELIVERED                120000

22.08.2015 UNDELIVERED          14

22.08.2015 FAILED                         5

Добавим тестовые данные:

INSERT INTO sms_message_dlv_status s
  (s.sms_message_id, s.dlv_status, s.dlv_status_at)
VALUES
  (1, 'DELIVERED', systimestamp);

INSERT INTO sms_message_dlv_status s
  (s.sms_message_id, s.dlv_status, s.dlv_status_at)
VALUES
  (2, 'UNDELIVERED', systimestamp);

INSERT INTO sms_message_dlv_status s
  (s.sms_message_id, s.dlv_status, s.dlv_status_at)
VALUES
  (3, 'FAILED', systimestamp);
  
INSERT INTO sms_message_dlv_status s
  (s.sms_message_id, s.dlv_status, s.dlv_status_at)
VALUES
  (4, 'FAILED', systimestamp);
  
INSERT INTO sms_message_dlv_status s
  (s.sms_message_id, s.dlv_status, s.dlv_status_at)
VALUES
  (5, 'FAILED', systimestamp);

INSERT INTO sms_message_dlv_status s
  (s.sms_message_id, s.dlv_status, s.dlv_status_at)
VALUES
  (6, 'UNDELIVERED', systimestamp);
  
INSERT INTO sms_message_dlv_status s
  (s.sms_message_id, s.dlv_status, s.dlv_status_at)
VALUES
  (7, 'UNDELIVERED', systimestamp);
  
INSERT INTO sms_message_dlv_status s
  (s.sms_message_id, s.dlv_status, s.dlv_status_at)
VALUES
  (8, 'DELIVERED', systimestamp);
  
INSERT INTO sms_message_dlv_status s
  (s.sms_message_id, s.dlv_status, s.dlv_status_at)
VALUES
  (9, 'DELIVERED', systimestamp);
  
INSERT INTO sms_message_dlv_status s
  (s.sms_message_id, s.dlv_status, s.dlv_status_at)
VALUES
  (10, 'DELIVERED', systimestamp);
  
INSERT INTO sms_message_dlv_status s
  (s.sms_message_id, s.dlv_status, s.dlv_status_at)
VALUES
  (11, 'DELIVERED', systimestamp);
  
COMMIT WORK;
/

Запрос:

SELECT s.dt, s.dlv_status, COUNT(*) AS "Количество"
  FROM (SELECT s.dlv_status AS dlv_status, trunc(s.dlv_status_at) AS dt
          FROM sms_message_dlv_status s) s
 GROUP BY s.dt, s.dlv_status;
/

P.S.
Конфигурация СУБД — Oracle Database 11g Express Edition Release 11.2.0.2.0
IDE — PL/SQL Developer Version 11.0.6.1776

Tags: Oracle, SQL