Как показать месячный результат с помощью SQL-запроса для следующего сценария?

У меня есть таблица базы данных с именем user_transaction, структура которой следующая:

transaction_id    mediumint(6)   UNSIGNED (PK)
transaction_no    varchar(55)
transaction_cc_avenue_no    varchar(55)
transaction_card_category    varchar(100)
transaction_user_id      varchar(32)
transaction_user_name    varchar(255)
transaction_user_email_id    varchar(255)
transaction_deal_code    varchar(10)
transaction_dc_id    smallint(4)
transaction_amount    float(10,2)
transaction_discount    float(10,2)
transaction_total_amount    float(10,2)
transaction_data_assign    enum('0', '1')
transaction_status    enum('success', 'inprocess', 'fail', 'cancelled')     
transaction_date    bigint(12)
transaction_update_date    bigint(12)
transaction_update_user_id    varchar(32)

Я использую значения UNIX Timestamp для хранения дат в столбце transaction_date. Теперь я хочу отображать месячный номер нет. транзакций с разным статусом_транзакции (т. е. общее количество транзакций, выполненных в каждую дату, общее количество транзакций со статусом «успешно», «в процессе», «сбой» и «отменено») для всех дат транзакций, представленных в таблице. Эти записи должны быть сгруппированы по месяцам. Я много пробовал, чтобы получить этот результат, но не добился никакого успеха. Для справки я даю ниже мой запрос:

SELECT COUNT(*) `total count`, SUM(transaction_status = 'success') `success`, SUM(transaction_status = 'inprocess') `inprocess`, SUM(transaction_status = 'fail') `fail`, SUM(transaction_status = 'cancelled') `cancelled` FROM user_transaction GROUP BY FROM_UNIXTIME(transaction_date)

Может ли кто-нибудь помочь мне в этом отношении? Заранее спасибо.


mysql php sql unix-timestamp
person PHPLover    schedule 12.07.2013    source источник
comment
@Eric Wich и @D Mac: я хочу показать номер месяца. хорошо. Как тогда написать запрос?   -  person PHPLover    schedule 12.07.2013
comment
Я обновил свой ответ, чтобы показать год и месяц (вам понадобится год, чтобы месяцы были устранены при изменении года).   -  person D Mac    schedule 12.07.2013
comment
@Eric Wich: Используя ваш запрос, я получаю результаты по дате, а не по месяцам.   -  person PHPLover    schedule 12.07.2013
comment
Вы изменили transaction_date на дату вместо bigint? И вы включили оператор GROUP BY MONTH (transaction_date)?   -  person D Mac    schedule 12.07.2013
comment
@DMac: Нет, я не менял тип данных transaction_date с bigint на date, а использовал GROUP BY MONTH (transaction_date)   -  person PHPLover    schedule 12.07.2013
comment
Вот почему это не работает. МЕСЯЦ (дата_транзакции) не работает, если дата_транзакции не является столбцом типа даты или даты и времени. МЕСЯЦ требует ввода даты или даты и времени.   -  person D Mac    schedule 12.07.2013
comment
Выполняя этот запрос, я получаю результаты, но вместо номера месяца отображаются mdates. Как показать только месяц и год? SELECT COUNT(*) total count, SUM(transaction_status = 'success') success, SUM(transaction_status = 'inprocess') inprocess, SUM(transaction_status = 'fail') fail, SUM(transaction_status = 'cancelled') cancelled FROM user_transaction GROUP BY FROM_UNIXTIME (дата_транзакции)   -  person PHPLover    schedule 12.07.2013

Ответы (1)


arrow_upward
1
arrow_downward

Будет намного проще, если вы просто установите transaction_date как дату. Затем вы можете использовать встроенные функции.

Например, вы можете сделать

SELECT year(transaction_date), month(transaction_date),
    sum(whatever), ...
FROM user_transaction
GROUP BY YEAR(transaction_date), MONTH(transaction_date)
ORDER BY YEAR(transaction_date), MONTH(transaction_date);

Если у вас есть отвращение к изменению типа данных transaction_date, вы можете преобразовать его на лету с помощью FROM_UNIXTIME. Я не рекомендую этого, потому что это дополнительный (ненужный) слой обработки в каждом запросе. Тем не менее, это означало бы, что вы можете оставить свою схему такой, какая она есть, и написать:

SELECT year(FROM_UNIXTIME(transaction_date)), month(FROM_UNIXTIME(transaction_date)),
    sum(whatever), ...
FROM user_transaction
GROUP BY YEAR(FROM_UNIXTIME(transaction_date)), MONTH(FROM_UNIXTIME(transaction_date))
ORDER BY YEAR(FROM_UNIXTIME(transaction_date)), MONTH(FROM_UNIXTIME(transaction_date));

Отвратительно, не так ли? Вам лучше сделать «ALTER TABLE CHANGE COLUMN transaction_date transaction_date date», и тогда вы получите все преимущества выполнения математических операций с датами с помощью простых в использовании функций MySQL.

person D Mac    schedule 12.07.2013