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
3 Réponses :
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;
lorsque j'utilise la syntaxe OVER, sql marque cette erreur. Je ne sais pas pourquoi?
@thuhaaa quelle est l'erreur?
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');
>>> 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 </br> 45097 9949 Non envoyé Envoyé </br> 45099 9950 Non envoyé Envoyé </br> 45101 9950 Envoyé non envoyé </br> 45104 9950 Non envoyé Envoyé </br> 45106 9929 Non envoyé Envoyé </br> 45108 9929 Envoyé non envoyé </br> 45109 9930 Non envoyé Envoyé </br> 45111 9930 Non envoyé Envoyé </br> 45113 9947 Envoyé non envoyé </br> 45116 9947 Non envoyé Envoyé </br> </br> ELK_List: 9947 </br>
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)
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?