-2
votes

Ordre des requêtes de syntaxe SQL par

SELECT TCID, START_TIME, RESULT, 
       cast(START_TIME as date) as m_date,
       max(cast(START_TIME as time)) as max_time
FROM jenkins_result.JENKINS_RESULT
WHERE TCID = 'A330506'
GROUP BY TCID, m_date;
This is my data:ID      TCID    START_DATE          RESULT
1545240 A435727 2020-11-08 03:11:43 PASS
1545334 A435727 2020-11-08 03:19:53 PASS
1547439 A435727 2020-11-09 03:11:52 PASS
1547621 A435727 2020-11-09 03:20:05 PASS
1548388 A435727 2020-11-09 07:51:29 PASS
1558801 A435727 2020-11-12 00:11:10 PASS
1561899 A435727 2020-11-12 08:48:59 PASSI want to get result of each TCID follow date like thisID      TCID    START_DATE          RESULT
1545334 A435727 2020-11-08 03:19:53 PASS
1548388 A435727 2020-11-09 07:51:29 PASS
1561899 A435727 2020-11-12 08:48:59 PASSBut the result current like that:1545240 A435727 2020-11-08 03:11:43 PASS    2020-11-08  03:19:53
1547439 A435727 2020-11-09 03:11:52 PASS    2020-11-09  07:51:29
1558801 A435727 2020-11-12 00:11:10 PASS    2020-11-12  08:48:59

sql

2 commentaires

Des échantillons de données d'entrée et de sortie aideraient grandement votre question.

L' order by semble correspondre au titre des questions. Quelle est ta question?

3 Réponses :

0
votes

Vous pouvez essayer ceci:

SELECT DISTINCT TCID
               ,START_TIME
               ,RESULT
               ,cast(START_TIME as date) as m_date
               ,max(cast(START_TIME as time)) OVER(PARTITION BY TCID) as max_time
FROM jenkins_result.JENKINS_RESULT
WHERE TCID='A330506'
ORDER BY TCID
        ,m_date;

qui devrait être le même que celui-ci:

SELECT DISTINCT TCID
               ,START_TIME
               ,RESULT
               ,cast(START_TIME as date) as m_date
               ,max(cast(START_TIME as time)) OVER() as max_time
FROM jenkins_result.JENKINS_RESULT
WHERE TCID='A330506'
ORDER BY TCID
        ,m_date;

ou si vous devez obtenir la valeur MAX par TCID :

SELECT TCID
      ,START_TIME
      ,RESULT
      ,cast(START_TIME as date) as m_date
      ,max(cast(START_TIME as time)) as max_time
FROM jenkins_result.JENKINS_RESULT
WHERE TCID='A330506'
GROUP BY TCID
        ,START_TIME
        ,RESULT
        ,cast(START_TIME as date)
ORDER BY TCID
        ,m_date;

2 commentaires

lorsque j'utilise la syntaxe OVER, sql marque cette erreur. Je ne sais pas pourquoi?

@thuhaaa quelle est l'erreur?

0
votes

J'ai créé un exemple d'entrée par la commande sql

select tbl_a.*
from tbl_mock as tbl_a, 
  (select tc, 
    cast(startdate as date) as m_date,
    max(cast(startdate as time)) as m_time
  from tbl_mock
  group by tc, m_date) as tbl_b
where tbl_a.tc = tbl_b.tc
and timestamp(tbl_b.m_date, tbl_b.m_time) = tbl_a.startdate

Vous pouvez essayer la commande sql ci-dessous pour obtenir votre résultat

create table tbl_mock (
  id int,
  tc int,
  startdate datetime,
  result varchar(20)
);

insert into tbl_mock(id, tc, startdate, result) values (1, 1, '2020/11/12 09:00:00', 'pass');
insert into tbl_mock(id, tc, startdate, result) values (2, 1, '2020/11/12 10:00:00', 'fail');
insert into tbl_mock(id, tc, startdate, result) values (3, 1, '2020/11/12 11:00:00', 'pass');
insert into tbl_mock(id, tc, startdate, result) values (4, 1, '2020/11/13 09:00:00', 'pass');
insert into tbl_mock(id, tc, startdate, result) values (5, 1, '2020/11/13 10:00:00', 'fail');
insert into tbl_mock(id, tc, startdate, result) values (6, 1, '2020/11/13 11:00:00', 'fail');

4 commentaires

>>> dummy_data1 { 'client': [ 'A_UQ_OAM_Line03_AD_4CRS_OAM_RAN_Test_Management_Test_Eping _Tu', 'tion_huong.dt3 B1_JIO_CP_70S_New_CP_PICO_Line5_CallControl_IntegrityProtec', 'A_KDDI_Essential_CP_Line05_02_IntraLTE_Redirection_phuongpv', 'SKT_A_FGE-SE0105_Operation_History_Management_A380365_P1_Tu nguyen', 'LGU_A_FGE-GM0104_Resource_Management_Mai2', 'A_VzW_CP_Line01_Volte_TTIB_UL_SPS_act_deact', 'TM500_P0_P1_Sprint_CP_CMAS_800M' , 'KDDI', 'KDDI'], 'résultat_jour': ['NOK', 'OK', 'OK', 'NOK', 'OK', 'OK', 'OK', 'OK', 'OK' ]} >>> data = pd.DataFrame (dummy_data1, columns = ['customer', 'daily_result'])

>>> data ['cus'] = np.where ('UQ' dans les données ['client'], "UQ", "a")

id report_id old_data new_data 45097 9949 Non envoyé Envoyé 45099 9950 Non envoyé Envoyé 45101 9950 Envoyé non envoyé 45104 9950 Non envoyé Envoyé 45106 9929 Non envoyé Envoyé 45108 9929 Envoyé non envoyé 45109 9930 Non envoyé Envoyé 45111 9930 Non envoyé Envoyé 45113 9947 Envoyé non envoyé 45476 9947 Non envoyé ELK_List: 99 Non envoyé

id report_id old_data new_data 45097 9949 Non envoyé Envoyé 45099 9950 Non envoyé Envoyé 45101 9950 Envoyé non envoyé 45104 9950 Non envoyé Envoyé 45106 9929 Non envoyé Envoyé 45108 9929 Envoyé non envoyé 45109 9930 Non envoyé Envoyé 45111 9930 Non envoyé Envoyé 45113 9947 Envoyé non envoyé 45116 9947 Non envoyé Envoyé ELK_List: 9947

0
votes

def update_to_es(self, index_name, index_step_name, data):
    if data.empty:
        logger.info('Nothing to update')
        print('Nothing to update')
        return
    data_sent = data.loc[data['new_data'] == "Sent"]
    data_unsent = data.loc[data['new_data'] == "Unsent"]
    ans = self.query_by_attr(index_name, 'report_id', data['Report_id'].tolist())
    report_id_list_exist = []
    for hit in ans['hits']['hits']:
        report_id_list_exist.append(hit['_source']['report_id'])
    report_id_sent_list = data_sent['Report_id'].tolist()
    report_id_unsent_list = data_sent['Report_id'].tolist()
    logger.info('Sent report id: {}'.format(report_id_sent_list))
    print('Sent report id: {}'.format(report_id_sent_list))
    logger.info('Unsent report id: {}'.format(report_id_unsent_list))
    print('Unsent report id: {}'.format(report_id_unsent_list))
    logger.info('Exist report id: {}'.format(report_id_list_exist))
    print('Exist report id: {}'.format(report_id_list_exist))
    # sent_list= [i for i in report_id_sent_list if i not in report_id_unsent_list
    #             and i not in report_id_list_exist]
    resent_list = [i for i in report_id_list_exist if i in report_id_sent_list and i not in report_id_unsent_list]
    # unsent_list = [i for i in report_id_unsent_list if i not in report_id_sent_list]
    # unsent_sent_list = [i for i in report_id_sent_list if i in report_id_unsent_list
    # and i in report_id_list_exist]
    sent_list = [i for i in report_id_sent_list if i not in resent_list]
    # delete unsent report
    if not data_unsent.empty:
        logger.info('Delete report id: {}'.format(report_id_unsent_list))
        print('Delete report id: {}'.format(report_id_unsent_list))
        self.delete_by_attr(index_name, 'report_id', report_id_unsent_list)
        self.delete_by_attr(index_step_name, 'report_id', report_id_unsent_list)
    # add data
    if not data_sent.empty:
        logger.info('Add report id: {}'.format(sent_list))
        print('Add report id: {}'.format(sent_list))
        data_update = self.get_data(sent_list)
        print(data_update)
        logger.info('Data update: {}'.format(data_update))
        data_update_step = self.convert_data_for_each_step(data_update)
        self.add_data(index_name, data_update)
        self.add_data(index_step_name, data_update_step)

0 commentaires