Tag Info

Hot answers tagged

5

You need one partition for that many records. Not 1000. Certainly not 1000/year. This is not a problem that requires partitioning. It looks to me like you've decided on the solution before fully stating and analysing the problem. Reading between the lines, it sounds like you're implementing a mulit-tenant system and have already decided that partitioning is ...


3

This online compression is extra cost option. And moreover usually online compression has worse compression ratio, then offline one. It's because the 1st one work on row level while the latter one works on block level. So if you really want to spare some space in the database and your old data are mostly read-only you should use: ALTER TABLE table_name MOVE ...


2

It can help query performance by employing partition elimination. This means large sections of big tables can be ignored when looking for values which means much less IO. Index alignment needs to be looked into when partitioning. See details here You can break your backups by partition. This can be useful if you are struggling to complete your backups in ...


2

You can use this one: SELECT custID, InteractionDate, IFNULL(ROUND((yes)/(yes+NO), 2),0) AS Success, sales FROM (SELECT custID, InteractionDate, sum(if(Purchased=='T',1,0)) AS yes, sum(if(Purchased!='T',1,0)) AS NO, sum(sales) AS sales FROM (SELECT 1 AS custID, 20150312 AS ...


2

A general goal in designing data storage and retrieval systems is that query time should scale with the amount of data being retrieved, not the amount of data that exists in total. Partitioning is a powerful tool for achieving that goal. Consider a table of session data that looks something like: create table clickstream ( clickstream_id bigserial ...


1

No one can tell you what boundaries your function should use but we can explain the behavior. Partition function timezoneoffset values are compared according to normal rules for comparing temporal data in SQL Server. The value '2012-01-01T00:00:00.000 -08:00' is equal to both '2012-01-01T01:00:00.000 -07:00' and '2012-01-01T02:00:00.000 -06:00'. Even ...


1

This would be very simple in SQL. As far as I can read the BigQuery Query reference, it supports all (GROUP BY clause, COUNT() and SUM() functions, CASE expressions): SELECT custID, InteractionDate, 1.0 * COUNT(CASE WHEN Purchased = 'T' THEN 1 END) / COUNT(*) AS Success, SUM(Sales) AS Sales FROM tableName GROUP BY custID, ...



Only top voted, non community-wiki answers of a minimum length are eligible