CODEWITHSUNDEEP
sqlvertica
user1559897
user1559897

Reputation: 1484

Sampling for a SQL Database

I have a column "steak" representing the amount of steak in pounds my firm has bought since day 1 of 2010. I have another column "c_steak" representing the cumulative sum of pounds of steak.

╔═══╦════════════╦═════════════╗
║   ║ steak      ║ c_steak     ║
╠═══╬════════════╬═════════════╣
║ 1 ║ 0.2        ║ 0.2         ║
║ 2 ║ 0.2        ║ 0.4         ║
║ 3 ║ 0.3        ║ 0.7         ║
╚═══╩════════════╩═════════════╝

How do I sample the table such that a row is taken once we buy another 100 pounds of steak? (sample ONE row immediately after c_steak reaches 100, 200, 300, 400 etc).

Note(EDIT):

  1. c_steak is float. It may not exactly hit 100, 200, 300....

  2. If c_steak goes like ..., 99.5, 105.3, 107.1, ... then the row corresponding to 105.3 will be sampled.

  3. if c_steak goes like ..., 99, 100.1, 100.2, 100.3, 105..., then the row corresponding to 100.1 will be sampled.

Upvotes: 2

Views: 222

Answers (4)

marcothesane
marcothesane

Reputation: 6749

You could have supplied some sample data.

Doing it now :

WITH    
-- sample data , this will be in the table                                                                    
input(id,steak_sold) AS (
          SELECT  1,30.07  
UNION ALL SELECT  2,30.01 
UNION ALL SELECT  3,30.02 
UNION ALL SELECT  4,30.03 
UNION ALL SELECT  5,30.04 
UNION ALL SELECT  6,30.05 
UNION ALL SELECT  7,30.06 
UNION ALL SELECT  8,30.07 
UNION ALL SELECT  9,30.08 
UNION ALL SELECT 10,30.09 
UNION ALL SELECT 11,30.10
UNION ALL SELECT 12,30.11
UNION ALL SELECT 13,30.12
UNION ALL SELECT 14,30.13
UNION ALL SELECT 15,30.14
UNION ALL SELECT 16,30.15
UNION ALL SELECT 17,30.16
)
-- real WITH clause would begin here: creating running sum myself ....
,
runsum AS (
SELECT
  *
, SUM(steak_sold) OVER(ORDER BY id) AS c_steak
FROM input
)
SELECT 
  *
FROM runsum
-- this running sum is above a certain 100 
-- previous (running sum - steak_sold) below that 100
-- integer division by 100 of the two differs
WHERE c_steak//100 <> (c_steak - steak_sold) //100;
-- out  id | steak_sold | c_steak 
-- out ----+------------+---------
-- out   4 |      30.03 |  120.13
-- out   7 |      30.06 |  210.28
-- out  10 |      30.09 |  300.52
-- out  14 |      30.13 |  420.98
-- out  17 |      30.16 |  511.43
-- out (5 rows)
-- out 
-- out Time: First fetch (5 rows): 53.018 ms. All rows formatted: 53.066 ms

Upvotes: 0

apomene
apomene

Reputation: 14389

It almost certain you need LAG method. You can try like:

SELECT *
FROM (
    SELECT c_steak
        ,lag(c_steak, 1, 0) OVER (ORDER BY id) lg
     FROM myTable
    ) sub
WHERE cast(sub.c_steak as int) %100 - cast(sub.lg as int)% 100 < 0

The logic is that when you reach a sum of 100, 200 etc, the difference in modulus with the previous value should be negative. e.g: 

80%100 = 80 where as 101%100 = 1
195%100 = 95 where as 205%100 = 5
293%100 = 93 where as 320%100 = 20
etc

Upvotes: 2

NybbleStar
NybbleStar

Reputation: 71

This works:

   SELECT m2.id,m2.steak,m2.c_steak FROM t1 as m1 inner join t1 as m2 on m2.id = m1.id + 1 WHERE cast(m2.c_steak as int) % 100 < cast(m1.c_steak as int) % 100;

Look here:

DEMO

===========

EDIT (in case id column skips at all):

   SELECT distinct m2.id,m2.steak,m2.c_steak FROM t1 as m1 inner join t1 as m2 on m2.id > m1.id WHERE cast(m2.c_steak as int) % 100 < cast(m1.c_steak as int) % 100;

DEMO

===========

Upvotes: 1

sorayadragon
sorayadragon

Reputation: 1087

You can use the MOD() function. Docs: https://dev.mysql.com/doc/refman/8.0/en/mathematical-functions.html#function_mod

SELECT * FROM Table WHERE MOD(c_steak, 100) = 0;

EDIT: In response to OPs edit, you can use FLOOR() on c_steak to get an int. Docs: https://dev.mysql.com/doc/refman/8.0/en/mathematical-functions.html#function_floor

SELECT * FROM Table WHERE MOD(FLOOR(c_steak), 100) = 0;

Upvotes: 0

Related Questions