Storing extremely small values in Amazon Redshift

Question

I am creating a table in Amazon Redshift using the following command:

CREATE TABLE asmt.incorrect_question_pairs_unique 
AS
SELECT question1,
       question2,
       occurrences,
       occurrences / (SUM(occurrences)::FLOAT) OVER () AS prob_q1_q2
FROM (SELECT question1,
             question2,
             SUM(occurrences) AS occurrences
      FROM asmt.incorrect_question_pairs
      GROUP BY question1,
               question2
      HAVING SUM(occurrences) >= 50)

I also tried an alternate:

CREATE TABLE asmt.incorrect_question_pairs_unique 
    AS
    SELECT question1,
           question2,
           occurrences,
           occurrences::float / SUM(occurrences) OVER () AS prob_q1_q2
    FROM (SELECT question1,
                 question2,
                 SUM(occurrences) AS occurrences
          FROM asmt.incorrect_question_pairs
          GROUP BY question1,
                   question2
          HAVING SUM(occurrences) >= 50)

I'd like the column prob_q1_q2 to be a float column, which is why I am converting the denominator/numerator to float. But in the resulting table, I get all zeros in that column.

I would like to point out that the SUM(occurrences) would amount to about 10 Billion, so the column prob_q1_q2 will contain extremely small values. Is there a way to store such small values in Amazon Redshift?

How do I make sure that all the values in the column are non-zero float?

Any help would be appreciated.

Answer 1

METHOD 1 - I have had the same problem! In my case it was million of rows so I Multiplied the result by 10000.
whenever I wanted to select values from that column I would divide by 10000 in the select statement to make it even.
I know its not the perfect solution but works for me.

METHOD 2 - I created a sample table with Numeric(12,6) datatype and when I imported the result set similar to yours, I can see the float values upto 6 decimal precision.

I guess, the conversion does not work when you use create table AS command, you need to create the table specifying the datatype which enforces the result set to be stored to a certain precision level.
Its odd! how the same select returns 0.00 but when inserted into table with enforced column, it returns 0.00333.

If I’ve made a bad assumption please comment and I’ll refocus my answer.