|
I need to store lots and lots and lots of timeseries data. Each series is keyed by a unique key, and once written it will almost never be updated. I'm literally after a key/value storage system that's persisted.
I also don't want to use Azure. No offense, Microsoft, but...
So my thoughts were Redis (not the safest), postgres (bit overkill), Cassandra (seems to like writes better than reads and my use case is the opposite), or mongoDB.
Did I mention I want this to cost less than a coffee a week. A good coffee, but I'm not paying $50 a month for this. Total data will be < 1TB.
Most likely this will be running on a Windows or Linux box against a .NET 5 app.
cheers
Chris Maunder
|
|
|
|
|
Chris Maunder wrote: and once written it will almost never be updated
I love specs like this - NOT.
Never underestimate the power of human stupidity -
RAH
I'm old. I know stuff - JSOP
|
|
|
|
|
I can promise you that X will never, ever, ever happen. Except sometimes randomly when I need it to happen.
cheers
Chris Maunder
|
|
|
|
|
Have you considered good old Berkeley DB? Oracle Berkeley DB It's not a DBMS, so might not fit your use case.
Keep Calm and Carry On
|
|
|
|
|
I heard the name Cassandra was a direct commentary on Oracle (Cassandra being a cursed Oracle, and all that)
cheers
Chris Maunder
|
|
|
|
|
Data and no "record counts" makes it hard to visualize a solution; or what needs to be done with the "time series data" subsequently; or how much of it there is for a given key.
I used a database table to index a file system of postal carrier address labels (images). Mostly used as an audit trail. The label key was a GUID.
It was only in wine that he laid down no limit for himself, but he did not allow himself to be confused by it.
― Confucian Analects: Rules of Confucius about his food
|
|
|
|
|
Imagine you load a file that has 3 sets of timeseries data: attributeA, attributeB and attributeC. Each timseries is of the form Array<time, float>. There will be between 1000 and 15,000 time/value pairs in each series, so maybe 8Kb to 120Kb in each series.
I will never query the data in the array. I will only ever return it as a chunk of data (meaning I could compress it into a BLOB for higher storage efficiency at a tradeoff in load speed if I needed to)
A classic database such as MySQL or SQL Server seems massive overkill for this.
cheers
Chris Maunder
|
|
|
|
|
Hi,
Trying to start a New Database in Open Office. Cannot do so, the system baulks at a missing JRE. Installed several of these, to no avail. Updated the Environment Path settings (now that brings me back 40 years)(That was on the advice on UTube from some indian Guru in these matters), Still to no avail. How can one get this DB to go! To be clear, I want (for now) to create a Database on and for one computer only.
Regards,
Bram.
Bram van Kampen
|
|
|
|
|
|
Well Richard,
No, Not so far, Thanks for the link. Going there next.
Bram van Kampen
|
|
|
|
|
Hi, everyone.
I'm trying to recover data from SQL Server using the pivot operator. My command is:
select isnull([1], 0) as Janeiro,
isnull([2], 0) as Fevereiro,
isnull([3], 0) as Março,
isnull([4], 0) as Abril,
isnull([5], 0) as Maio,
isnull([6], 0) as Junho,
isnull([7], 0) as Julho,
isnull([8], 0) as Agosto,
isnull([9], 0) as Setembro,
isnull([10], 0) as Outubro,
isnull([11], 0) as Novembro,
isnull([12], 0) as Dezembro
from (select month(PP.PAPA_DT_DATAPAGAMENTO) as Mesn, PP.PAPA_RE_VALORPAGAMENTO from PAGAMENTO_PARCELA PP, PAGAMENTO P
where PP.MOVI_NA_CODIGO = P.MOVI_NA_CODIGO AND P.SUPA_NA_CODIGO = 5) Tab1
pivot (sum(PAPA_RE_VALORPAGAMENTO) for Mesn in ([1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12])) P1
The way it is above it works fine. Now I need to multiply the sum by a factor, say 0.95. I tried the following:
pivot (0.95 * sum(PAPA_RE_VALORPAGAMENTO) ...
pivot (sum(0.95 * PAPA_RE_VALORPAGAMENTO) ...
0.95 * pivot (sum(PAPA_RE_VALORPAGAMENTO) ...
None of these worked. Can anyone help me? Which is the right way?
Thanks.
|
|
|
|
|
"None of these worked" because the syntax for PIVOT[^] clearly states Quote: PIVOT
(
<aggregation function="">(<column being="" aggregated="">)
So just put the results of the PIVOT into a sub-query or a Common Table Expression or temporary table or a table variable, then manipulate that data. E.g.
;with cte as
(
select isnull([1], 0) as Janeiro,
isnull([2], 0) as Fevereiro,
isnull([3], 0) as Março,
isnull([4], 0) as Abril,
isnull([5], 0) as Maio,
isnull([6], 0) as Junho,
isnull([7], 0) as Julho,
isnull([8], 0) as Agosto,
isnull([9], 0) as Setembro,
isnull([10], 0) as Outubro,
isnull([11], 0) as Novembro,
isnull([12], 0) as Dezembro
from (select month(PP.PAPA_DT_DATAPAGAMENTO) as Mesn, PP.PAPA_RE_VALORPAGAMENTO from PAGAMENTO_PARCELA PP
inner join PAGAMENTO P on PP.MOVI_NA_CODIGO = P.MOVI_NA_CODIGO
where P.SUPA_NA_CODIGO = 5) Tab1
pivot (sum(PAPA_RE_VALORPAGAMENTO) for Mesn in ([1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12])) P1
)
select 0.95 * Janeiro,0.95 * Fevereiro,0.95 * Março,0.95 * Abril,
0.95 * Maio,0.95 * Junho,0.95 * Julho,0.95 * Agosto,
0.95 * Setembro,0.95 * Outubro,0.95 * Novembro,0.95 * Dezembro
from cte; Couple of other points to note
- I've changed the JOIN to use an ON clause rather than defining the join using the WHERE clause. Your style is quite old-fashioned and prevents you from using OUTER joins
- It's a lot easier to answer questions like this if you supply the table schemas and some sample data along with your expected results. And always be specific - "None of these worked" is not helpful. "I get an error reported 'Incorrect syntax near '0.95'.' might have got you an answer quicker
|
|
|
|
|
Hi,
as you act on a column within the select "......" statements,
you must add "group by" and the column involved.
It's from Sql engine,
in a step : it gather the columns,
in a late step : a rollback is made to apply the 0.95* operation on all selected columns.
so the "group by" recall all the 'columns' for sorting the "whole gathered" one by one.
It's really inner mechanicals SqlEngine work.
Remenber : when operation on columns in the 'select' : as "label" / maths .. , string works
add "group by" with the column required.
modified 23-Aug-21 21:01pm.
|
|
|
|
|
I'm really not following this at all. Perhaps if you make the amendments you are speaking about and post that code here I might understand it better?
Or if you have a link to the specific documentation that might help?
|
|
|
|
|
one other error I see :
from -->"tables required"<-- where column-n =<.. ( sub request ).
add as fix the table needed for the first query ( the first part )
I think you have to go on taxes early ,
insert into tax_tab (n_amount, paid) values amount=n_amount , 0.95*n_amount ;;
modified 23-Aug-21 21:01pm.
|
|
|
|
|
Sorry, I still do not understand what you are saying. Please post the code here to illustrate your point.
There are no errors reported in my code - or do you think you are replying to the Original Poster?
|
|
|
|
|
Hello:
My MySQL database version is 5.6.35 and I use InnoDB.
I want to remove the Delete and Drop Table permissions of the user U1 from the table T1 in the db1 database.
The operating instructions are as follows:
<pre lang="SQL">
Revoke delete,drop on db1.T1 from U1;
No error message appeared after execution.
But after execution, the user U1 can still use the DELETE command to delete the data row in the data table T1.
You can also use the DROP TABLE command to delete the data table T1.
Thanks for replying.
|
|
|
|
|
|
Hi,
I am looking a sandwich query for my attendance module, If the employee is absent on Saturday and Monday then sunday will be calculate as Absent the same has to be case of holidays.
The attendance table structure in SQL 2005 as per below.
EmpCode
EmpShift
AttDate
AttStatus
.
.
Please Advice/provide some solution for the above.
Thanks & Kind Regards
Mohammad Salmani
|
|
|
|
|
Mohammad Salmani wrote: provide some solution
Sorry, this site does not provide code to order. Google will find you many SQL tutorials that will help.
|
|
|
|
|
LEFT JOIN to a table that has all dates and find those that do not have a match.
|
|
|
|
|
I am having the below data in Attendance Table
EmpCode AttMonth AttYear AttDate ShiftCode AttStatus
1 6 2021 2021-06-01 00:00:00.000 1001 P
1 6 2021 2021-06-02 00:00:00.000 1001 P
1 6 2021 2021-06-03 00:00:00.000 1001 P
1 6 2021 2021-06-04 00:00:00.000 1001 P
1 6 2021 2021-06-05 00:00:00.000 1002 A
1 6 2021 2021-06-06 00:00:00.000 1009 WO
1 6 2021 2021-06-07 00:00:00.000 1001 A
1 6 2021 2021-06-08 00:00:00.000 1001 P
1 6 2021 2021-06-09 00:00:00.000 1001 P
I need to update 'WO' as 'A' while processing attendance
please help for the above.
|
|
|
|
|
I can't help because I do not understand what you need help with.
|
|
|
|
|
A few SQL functions may be of help for you ..
Take a look at this query
SELECT *,DATEPART(wk, AttDate) As 'Week Number',DATEPART(weekday, AttDate) As 'Week Day', DATENAME(WEEKDAY,ATTDATE) FROM #TEMP1
|
|
|
|
|
I would do the as 3 queries:
1 get the first absent date date - store it in date1 variable
2 get the next absent date > date 1 - if it exists store it in date2 variable
If date2 is not null (more than 1 day)
3 set all records between date1 and date2 to absent
This covers multi day holidays.
Never underestimate the power of human stupidity -
RAH
I'm old. I know stuff - JSOP
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
