Блог

Главная - Вопросы по программированию - Запрос SQL / Hive для подсчета количества строк за каждый день для определенного значения

Запрос SQL / Hive для подсчета количества строк за каждый день для определенного значения

#python #sql #hive #data-science #hiveql

#python #sql #улей #наука о данных #hiveql

Вопрос:

В настоящее время я работаю над скриптом Python, который использует запрос для извлечения данных с нашего сервера Hive. Я ожидаю результат, который будет фильтровать номера карт, которые имеют x или более транзакций в день, в зависимости от ввода для «TxnCount».

Входными данными являются: DateTime1, DateTime2, MerchantId, CardNum, terminalID и TxnCount.

Мой код (не работает): 

 Query = "SELECT TRIM(i002_number) as CardNum, i004_amt_trxn, TRIM(i042_merch_id) as MerchantID, i043a_merch_name, TRIM(i041_pos_id) as TerminalID, 
i049_cur_trxn, i062v2_trans_id, i003_proc_code, i006_amt_bill, i051_cur_bill, amt_card, cardcurrency, ltimestamp, 
i039_rsp_cd, i018_merch_type, i043b_merch_city, i043c_merch_cnt, i022_pos_entry, i032_acquirer_id, trxntype, reasoncode, 
SUBSTRING(i002_number, 1, 6) AS issuer_bin, COUNT(i002_number) as txncount
CASE 
    WHEN SUBSTRING(i002_number,1,1) = 5 THEN 'MasterCard' 
    WHEN SUBSTRING(i002_number,1,1) = 4 THEN 'VISA' 
END AS source 
FROM tsys.ods_authorizations 
WHERE ltimestamp &&t;= '" DateTime1 "' AND ltimestamp <= '" DateTime2 "' AND i042_merch_id = " MerchantID " 
AND i002_number = " CardNum " AND i041_pos_id = " terminalID ""
HAVING txncount &&t;= '" TxnCount "'

Пример ожидаемых данных (усеченный): 

 CardNum         TimeStamp           TxnCount
123      2019-06-01 00:00:30.00        2   
123      2019-06-01 05:00:20.00        2
123      2019-06-03 20:00:00.00        1
456      2019-06-04 06:00:00.00        2
456      2019-06-04 00:00:10.91        2
789      2019-06-01 12:00:40.51        1

Я думаю, что моя проблема здесь в том, что он не может подсчитываться по номеру карты, поскольку у меня возникают проблемы с предложением GROUP BY . Кроме того, я еще не разделил дату и время, и запрос еще не распознал бы разницу между датами.

Комментарии:

1. Просто добавлю: как вы можете видеть в части выбора, мне нужно вывести около 20 столбцов. Если это не так, я могу легко решить эту проблему, добавив: GROUP BY i002_number, DATE (ltimestamp)

2. Как насчет добавления простого GROUP BY TRIM(i002_number),ltimestamp и удаления всего остального из условия ВЫБОРА. Но если вам нужны все столбцы и количество, тогда используйте COUNT(*) OVER (PARTITION BY TRIM(i002_number),ltimestamp ORDER BY TRIM(i002_number),ltimestamp ) as CNT . HTH

3. Привет @KoushikRoy, это работает действительно хорошо, спасибо. У меня пока недостаточно очков, чтобы проголосовать за ответ. Но это для того, чтобы вы знали, что ваше решение работает.

4. @KoushikRoy Следующий вопрос, это не работает, когда у меня нет предложения «HAVING». Каким может быть решение, поскольку мне также нужно иметь это предложение HAVING, чтобы запрос принимал входные данные для TxnCount.

5. Я этот запрос не соответствует некоторым основам. Я считаю, что приведенное ниже решение от Гордона должно сработать. Если этого не происходит, пожалуйста, добавьте ПОРЯДОК следующим образом COUNT(*) OVER (PARTITION BY i002_number, TRUNC(ltimestamp, 'DAY') ORDER BY i002_number, TRUNC(ltimestamp, 'DAY') ) as txncount

Ответ №1:

Ваш запрос неверно сформирован. У вас есть COUNT() с кучей других столбцов — и у вас их нет GROUP BY . Это не разрешено в SQL.

Я бы посоветовал вам также использовать параметры, а не перегружать строку запроса. Итак, вы, вероятно, хотите что-то вроде этого.

Похоже, что ваши результаты требуют подробностей каждой транзакции, а не сводки (таким образом, у вас есть две строки со счетом «2», а не одна). Это говорит о том, что вам действительно нужны оконные функции:

 SELECT a.*
FROM (SELECT a.*,
             (CASE WHEN i002_number LIKE '5%' THEN 'MasterCard'
                   WHEN i002_number LIKE '4%' THEN 'VISA'
              END) AS source
             COUNT(*) OVER (PARTITION BY i002_number, TRUNC(ltimestamp, 'DAY')) as txncount
      FROM FROM tsys.ods_authorizations a
      WHERE ltimestamp &&t;= :timestamp1 AND
            ltimestamp <= :timestamp2 AND
            i042_merch_id = :MerchantID AND
            i002_number = :CardNum AND 
            i041_pos_id = :terminalID
     ) a
WHERE txncount &&t;= :TxnCount