Sqlite db上Python中的行排序和选择逻辑

Question

您好，谢谢您抽出时间来回答我的问题。我在一个小城市的预算空间工作，在这段不稳定的时间里，我正在学习一些python，也许将来可以帮助我进行一些金融数据建模。我们目前使用SAP，但我也想学习一门新的语言

我需要一些关于在哪里寻找某些答案的指示。 例如，我建立了一个包含数百万条记录的数据库，按日期和时间排序。我能够剥离我不需要的数据，现在有一个干净的数据库来处理

从高层次上讲，我想知道，根据一天中的第一条记录，是否在同一天有另一条记录是第一条记录的两倍

Date|time|dept|Value1
01/01/2019|11:00|BUD|51.00
01/01/2019|11:30|CSD|101.00
01/01/2019|11:50|BUD|102.00
01/02/2019|10:00|BUD|200.00
01/02/2019|10:31|BUD|201.00
01/02/2019|11:51|POL|400.00
01/03/2019|11:00|BUD|100.00
01/03/2019|11:30|PWD|101.00
01/03/2019|11:50|BUD|110.00

基于上述数据和要求，我想得到

Date|time|dept|Value| Start Value
01/01/2019|11:50|BUD|102.00|51.00
01/02/2019|11:51|POL|400.00|200.00
01/03/2019|NONE|NONE|NONE|100.00

在第3天，没有至少是两倍的值，因此，我们没有或为空

到目前为止我做了什么

我已经能够连接到数据库[python] 2.我能够从数据库[sqlite]中删除不必要的信息和部门 3.我已经能够为result[Python]创建新表

问题/最佳做法

1. 如何获得每天的第一条线路。我是否从分配给2019年1月1日的循环之前的一个变量开始，然后选择行号并将其存储在另一个表中，或者我们这里还有什么其他选项
2. 在另一个表或数组中存储/捕获每天的第一行后，如何获取第一行中至少两次出现的值

前任？开始元代码**********

Start from Line 1 to end
table2.date[] Should be equal to 01/01/2019
table2.value[] Should be equal to 51.00
look through each line if date = table2.date and value >= 2* (table2.value[])
*if successful, get record line number and department and value and store in new table
else
goto next line

Then increase table2.date and table2.value by 1 and do the loop again. 

结束元代码*****************

这是正确的方法吗？我觉得每次日期更改都要检查数百万条记录，但这并不是非常优化的

如果date不等于table2.date[1]，我可能会添加一个退出条件，但我仍然不确定这是否是解决此问题的正确方法。这将只运行一次或两次，一年，所以系统性能不是那么重要，但我仍然在考虑如何接近它的正确方式

1. 我应该将最终数据导出到excel中进行分析，还是用Python作为良好的分析建模工具。专业人士会推荐什么

Answer 1

你可以这样做，但你是对的，这需要很长时间。我不知道SQLite是否有能力有效地做您想要做的事情，但我知道Python有。听起来您可能想使用Python数据分析库Pandas。您可以在此处了解如何将SQLite导入Pandas：

How to open and convert sqlite database to pandas dataframe

一旦你在一个Pandas数据帧中有了它，就有很多函数来获取第一次出现的数据，找到重复的数据，找到唯一的值，甚至生成只有唯一值的其他数据帧

Answer 2

您可以使用exists检查同一天是否存在另一条记录，该记录的值是否大于两倍，并使用窗口功能每天对顶部记录进行筛选：

select *
from (
    select 
        t.*,
        row_number() over(partition by date order by time) rn
    from mytable t
    where exists (
        select 1 from mytable t1 where t1.date = t.date and t1.value = 2 * t.value
    )
) t
where rn = 1

在row_number()不可用的SQLite版本中，另一个选项是使用相关子查询进行筛选：

select t.*
from mytable t
where 
    exists(select 1 from mytable t1 where t1.date = t.date and t1.value = 2 * t.value)
    and t.time = (select min(t1.time) from mytable t1 where t1.date = t.date)