accounts_logs = session.query(log_custom.updated_at,log_custom.amount,log_custom.receivable,log_custom.custom,log_custom.accounts_id,log_custom.user_id).filter(log_custom.custom==custom).all()如何根据时间倒序

你可以在查询时使用`order_by`方法来实现根据时间的倒序排列。具体来说，你可以将`order_by`方法传递给查询对象的参数，并按照`updated_at`字段进行排序，如下所示：

accounts_logs = session.query(log_custom.updated_at, log_custom.amount, log_custom.receivable, log_custom.custom, log_custom.accounts_id, log_custom.user_id)\
                .filter(log_custom.custom == custom)\
                .order_by(log_custom.updated_at.desc())\
                .all()

这将按照`updated_at`字段的降序排列结果。

相关问题

优化sql：SELECT we.organization_id ,we.wip_entity_id ,case when wl.line_id is null then we.wip_entity_name else '' end wip_entity_name ,we.primary_item_id ,mtt.transaction_type_name ,mmt.transaction_date ,bd.department_code ,mmt.inventory_item_id ,mmt.subinventory_code ,mta.reference_account ,br.resource_code ,lu2.meaning as line_type_name ,mta.base_transaction_value ,mta.cost_element_id ,flv.meaning as cost_element ,wdj.class_code job_type_code ,ml.meaning job_type_name FROM (select * from gerp.mtl_material_transactions where substr(transaction_date,1,7) >= '2023-06' and transaction_source_type_id = 5) mmt inner join gerp.wip_entities we on mmt.organization_id = we.organization_id inner join gerp.mtl_transaction_accounts mta on mta.transaction_source_id = we.wip_entity_id and mta.transaction_id = mmt.transaction_id and mta.transaction_source_type_id = 5 inner join gerp.mtl_transaction_types mtt on mtt.transaction_type_id = mmt.transaction_type_id inner join mfg_lookups lu2 on lu2.lookup_code = mta.accounting_line_type and lu2.lookup_type = 'CST_ACCOUNTING_LINE_TYPE' inner join gerp.mtl_system_items_b msi on msi.inventory_item_id = mmt.inventory_item_id and msi.organization_id = mta.organization_id left join gerp.bom_departments bd on bd.department_id = mmt.department_id left join gerp.bom_resources br on br.resource_id = mta.resource_id left join gerp.wip_lines wl on wl.line_id = mmt.repetitive_line_id left join gerp.wip_discrete_jobs wdj on wdj.wip_entity_id = mta.transaction_source_id left join gerp.fnd_lookup_values_vl flv on cast(mta.cost_element_id as string) = flv.lookup_code and flv.lookup_type = 'CST_COST_CODE_TYPE' left join mfg_lookups ml on ml.lookup_code = wdj.job_type and ml.lookup_type = 'WIP_DISCRETE_JOB' 。其中mmt,we,mta,msi,wdj数据量很大

针对这个SQL语句，可以优化的地方有：

1. 使用JOIN语句时，可以根据数据量大小，将数据量大的表放在后面，这样可以避免在连接时出现性能问题。

2. 使用子查询时，可以在子查询中添加条件筛选，减少返回的数据量。

3. 可以对查询中的日期条件进行优化，例如使用日期范围查询时，可以使用BETWEEN关键字代替大于等于和小于等于的运算符。

4. 可以对大表进行分区或分片，以加快查询速度。

5. 可以对JOIN语句中的字段添加索引，以提高查询效率。

下面是对原SQL语句的优化：

SELECT 
    we.organization_id, 
    we.wip_entity_id, 
    CASE WHEN wl.line_id is null THEN we.wip_entity_name ELSE '' END wip_entity_name,
    we.primary_item_id, 
    mtt.transaction_type_name, 
    mmt.transaction_date, 
    bd.department_code, 
    mmt.inventory_item_id, 
    mmt.subinventory_code, 
    mta.reference_account, 
    br.resource_code, 
    lu2.meaning as line_type_name,
    mta.base_transaction_value, 
    mta.cost_element_id, 
    flv.meaning as cost_element, 
    wdj.class_code job_type_code, 
    ml.meaning job_type_name 
FROM 
    gerp.wip_entities we 
    INNER JOIN (
        SELECT 
            * 
        FROM 
            gerp.mtl_material_transactions 
        WHERE 
            transaction_date BETWEEN '2023-06-01' AND '2023-06-30' 
            AND transaction_source_type_id = 5
    ) mmt ON mmt.organization_id = we.organization_id 
    INNER JOIN gerp.mtl_transaction_accounts mta ON mta.transaction_source_id = we.wip_entity_id 
        AND mta.transaction_id = mmt.transaction_id 
        AND mta.transaction_source_type_id = 5 
    INNER JOIN gerp.mtl_transaction_types mtt ON mtt.transaction_type_id = mmt.transaction_type_id 
    INNER JOIN mfg_lookups lu2 ON lu2.lookup_code = mta.accounting_line_type AND lu2.lookup_type = 'CST_ACCOUNTING_LINE_TYPE' 
    INNER JOIN gerp.mtl_system_items_b msi ON msi.inventory_item_id = mmt.inventory_item_id 
        AND msi.organization_id = mta.organization_id 
    LEFT JOIN gerp.bom_departments bd ON bd.department_id = mmt.department_id 
    LEFT JOIN gerp.bom_resources br ON br.resource_id = mta.resource_id 
    LEFT JOIN gerp.wip_lines wl ON wl.line_id = mmt.repetitive_line_id 
    LEFT JOIN gerp.wip_discrete_jobs wdj ON wdj.wip_entity_id = mta.transaction_source_id 
    LEFT JOIN gerp.fnd_lookup_values_vl flv ON cast(mta.cost_element_id as string) = flv.lookup_code 
        AND flv.lookup_type = 'CST_COST_CODE_TYPE' 
    LEFT JOIN mfg_lookups ml ON ml.lookup_code = wdj.job_type AND ml.lookup_type = 'WIP_DISCRETE_JOB';

在优化后的SQL语句中，将子查询中的日期范围查询放在了WHERE语句中，将数据量较大的表放在了后面，左连接的表也放在了后面。同时，可以根据具体情况对需要添加索引的字段进行索引优化。

SELECT hd.id, ar.id AS areaId, hd.PASSWORD, hd.NAME AS username, hd.sex, hd.mobile AS phone, hd.card_number AS cardNumber, hd.address AS address, hd.avatar AS avatarUrl, hd.house_number AS room, hd.sum_integration AS bonus, hd.retrievable_integration AS retrievableIntegration, hd.harmful_integration AS harmfulIntegration, hd.foodwaste_integration AS foodwasteIntegration, hd.use_integration AS useIntegration, hd.other_integration AS otherIntegration, hd.village AS areaName, hd.pid, hd.division_id AS divisionId, hd.login_degree AS loginDegree, hd.province, hd.city, hd.county, hd.street, hd.qr_code AS qrCode, hd.barcode, hd.STATUS, FROM_UNIXTIME( hd.create_time ) AS createTime, FROM_UNIXTIME( hd.update_time ) AS updateTime, hd.oldcard, hd.accounts_code AS accountsCode, hd.type, hd.token, hd.openid, hd.tong_interal AS tongInteral, hd.tong_interal1 AS tongInteral1, hd.vali, CONCAT( '[', hm.number, ']', hm.NAME ) AS homeName, FROM_UNIXTIME( hi.create_time ) AS deliverTime FROM hs_household hd LEFT JOIN hs_management hm ON hd.division_id = hm.id LEFT JOIN hs_area ar ON ar.id = 23 LEFT JOIN hs_integration_log hi ON hi.user_code = hd.id WHERE ( hd.province = ar.NAME OR hd.city = ar.NAME OR hd.county = ar.NAME OR hd.street = ar.NAME ) GROUP BY hd.id LIMIT 1, 10这条sql怎么加索引

这条 SQL 中涉及到的表有 hs_household、hs_management、hs_area、hs_integration_log，您需要根据具体情况来确定需要为哪些字段添加索引。以下是一些可能需要添加索引的字段：

1. hs_household 表的字段：card_number、mobile、division_id
2. hs_management 表的字段：id
3. hs_area 表的字段：id、NAME
4. hs_integration_log 表的字段：user_code、create_time

您可以使用如下命令添加索引：

1. 为 hs_household 表的 card_number、mobile、division_id 字段添加索引：

ALTER TABLE hs_household ADD INDEX idx_card_number (card_number);
ALTER TABLE hs_household ADD INDEX idx_mobile (mobile);
ALTER TABLE hs_household ADD INDEX idx_division_id (division_id);

2. 为 hs_management 表的 id 字段添加索引：

ALTER TABLE hs_management ADD INDEX idx_id (id);

3. 为 hs_area 表的 id、NAME 字段添加索引：

ALTER TABLE hs_area ADD INDEX idx_id (id);
ALTER TABLE hs_area ADD INDEX idx_name (NAME);

4. 为 hs_integration_log 表的 user_code、create_time 字段添加索引：

ALTER TABLE hs_integration_log ADD INDEX idx_user_code (user_code);
ALTER TABLE hs_integration_log ADD INDEX idx_create_time (create_time);

当然，您需要根据具体情况来确定哪些字段需要添加索引，避免添加过多的索引导致查询性能下降。