Differences
This shows you the differences between two versions of the page.
|
en:scripts:python:percentile [2017/01/21 02:42] alex created |
en:scripts:python:percentile [2017/01/21 02:55] (current) alex |
||
|---|---|---|---|
| Line 1: | Line 1: | ||
| - | ====== stdev for SQLite ====== | + | ====== percentile for SQLite ====== |
| SQLite does not have a function for percentile. Not really sure why. Fortunately, the sqlite3 library included in Python supports user-defined functions. Here is a test script that creates a user-defined aggregate function for percentile and then runs it with a set of test values. The implementation of percentile skips null values and retuns null when it does not get enough points. | SQLite does not have a function for percentile. Not really sure why. Fortunately, the sqlite3 library included in Python supports user-defined functions. Here is a test script that creates a user-defined aggregate function for percentile and then runs it with a set of test values. The implementation of percentile skips null values and retuns null when it does not get enough points. | ||
| Line 26: | Line 26: | ||
| return None | return None | ||
| self.list.sort() | self.list.sort() | ||
| - | return self.list[int(round(len(self.list)*self.percent/100.0))] | + | return self.list[int(round((len(self.list)-1)*self.percent/100.0))] |
| with sqlite3.connect(':memory:') as con: | with sqlite3.connect(':memory:') as con: | ||