CODEWITHSUNDEEP
sqlvertica
James W.
James W.

Reputation: 3055

vertica generate table with numbers & select prime numbers

I'm working on Vertica version 8+

I need to create a table with numbers from 1 to N and select the prime numbers

I've accomplished to do that in SQLite3 but Vertica doesn't allow recursive WITH

https://forum.vertica.com/discussion/206185/recursive-queries

Edit:

WITH seq AS (
  SELECT n FROM (
    SELECT ROW_NUMBER() OVER() AS n 
    FROM (
        SELECT 1 
        FROM (
            SELECT date(0) + INTERVAL '1 second' AS i 
            UNION ALL
            SELECT date(0) + INTERVAL '100 seconds' AS i 
        ) _
        TIMESERIES tm AS '1 second' OVER(ORDER BY i)
    ) _ 
  ) _ 
  WHERE n > 1 -- 1 is not prime number
)

SELECT n 
FROM (SELECT n FROM seq) _  
WHERE n NOT IN (
  SELECT n FROM (
    SELECT s1.n AS n, s2.n AS n2
    FROM seq AS s1 
    CROSS JOIN seq AS s2
    ORDER BY n, n2
  ) _
  WHERE n2 < n AND n % n2 = 0
)
ORDER BY n

Upvotes: 2

Views: 2234

Answers (1)

mauro
mauro

Reputation: 5950

You don't need recursive WITH for this.

First Step. You need to generate all numbers from 1 to N (let's say 1000). You can easily do this in Vertica using TIMESERIES. The following will generate all numbers from 1 to 1000:

WITH seq AS (
    SELECT ROW_NUMBER() OVER() AS num FROM (
        SELECT 1 FROM (
            SELECT date(0) + INTERVAL '1 second' AS se UNION ALL
            SELECT date(0) + INTERVAL '1000 seconds' AS se ) a
        TIMESERIES tm AS '1 second' OVER(ORDER BY se)
    ) b
)
SELECT num FROM seq ; 
 num  
------
    1
    2
    3
    4
  ...
  997
  998
  999
 1000

Second Step we just have to exclude non-prime numbers from the result set here above (see here, here and here):

WITH seq AS (
    SELECT ROW_NUMBER() OVER() AS num FROM (
        SELECT 1 FROM (
            SELECT date(0) + INTERVAL '1 second' AS se UNION ALL
            SELECT date(0) + INTERVAL '1000 seconds' AS se ) a
        TIMESERIES tm AS '1 second' OVER(ORDER BY se)
    ) b
)
SELECT num as prime 
FROM seq
WHERE num NOT IN (
    SELECT s1.num * s2.num
    FROM seq s1 CROSS JOIN seq s2
        WHERE s1.num BETWEEN 2 AND CEILING (SQRT (1000)) 
        AND s1.num <= s2.num
        AND s2.num * s1.num <= 1000
)
ORDER BY 1
;
 prime 
-------
     1
     2
     3
     5
     7
    11
   ...
   977
   983
   991
   997

Upvotes: 3

Related Questions