I would have included a pull request in this suggestion, except it doesn't look like the Git is up to date.
Components
SteemSQL is an incredibly useful service for those of us that regularly work with data from the blockchain. @arcange has made the service better and better since it became a subscription model. New upgrades and tables have created an added benefit to those of us that use the service. Unfortunately, some of the values in these columns can make the data unnecessarily difficult to work with.
For example, in the "Accounts" table, vesting_shares variables tend to be string values that look like this: "1111.024 VESTS".
The word "VESTS" is unnecessary, and makes the numerical data difficult to deal with.
Other examples in the same table, include the balance columns, which tend to look like this:
"122.24 STEEM"
Proposal
My proposal is that, in cases where numerical values (especially in cases related to VESTS and currencies) are represented as strings, the alphabetical values be stripped out and the variable be converted to a float variable.
Mockups / Examples
I have been doing this in my own scripts as follows:
SQLaccounts= '''
select
name,vesting_shares,delegated_vesting_shares,received_vesting_shares
from
Accounts (NOLOCK)
'''
Accountdf = pd.read_sql(SQLaccounts, conn)
Accountdf['vesting_shares'] = Accountdf['vesting_shares'].str.replace('VESTS', '')
Accountdf['vesting_shares'] = Accountdf['vesting_shares'].astype(float)
Accountdf['received_vesting_shares'] = Accountdf['received_vesting_shares'].str.replace('VESTS', '')
Accountdf['received_vesting_shares'] = Accountdf['received_vesting_shares'].astype(float)
Accountdf['delegated_vesting_shares'] = Accountdf['delegated_vesting_shares'].str.replace('VESTS', '')
Accountdf['delegated_vesting_shares'] = Accountdf['delegated_vesting_shares'].astype(float)
I use pandas DataFrames as an easier way to organize my data, but the important piece of this code is:
.str.replace('VESTS', '')
and
.astype(float)
In python, it is a simple thing to strip the unnecessary alphabetical data and convert the variables to float values.
If there is a more current Git associated with this project, I would be happy to create a pull request.
Benefits
If this were done across the database, it would reduce the time and complexity involved in analyzing blockchain data using SteemSQL and make the data more accessible to less technologically-savvy users.
Posted on Utopian.io - Rewarding Open Source Contributors