{"id":46740,"date":"2022-12-16T00:00:00","date_gmt":"2022-12-16T08:00:00","guid":{"rendered":"https:\/\/griddb-linux-hte8hndjf8cka8ht.westus-01.azurewebsites.net\/blog\/analysis-of-the-swisslos-lottery-using-r-and-griddb\/"},"modified":"2025-11-13T12:56:24","modified_gmt":"2025-11-13T20:56:24","slug":"analysis-of-the-swisslos-lottery-using-r-and-griddb","status":"publish","type":"post","link":"https:\/\/griddb.net\/en\/blog\/analysis-of-the-swisslos-lottery-using-r-and-griddb\/","title":{"rendered":"Analysis of the Swisslos lottery using R and GridDB"},"content":{"rendered":"

A superstitious person may believe they are more likely to win the lottery by playing their lucky numbers. They believe there is a greater chance that their chosen numbers will be drawn.<\/p>\n

Of course, unless the lottery is rigged, that is not the case. Most will realize that every number and every combination of numbers has the same likelihood. However – and this is the exciting part – not every combination has the same expected value.<\/p>\n

Our idea is this: Because some people are superstitious, and humans are bad at picking numbers randomly, there will be numbers that gamblers play much more frequently than other numbers. If we can play the numbers that other people are least likely to play, we expect to win more money because if we do actually win, the pot will be split among fewer people.<\/p>\n

So, it is true that some numbers will make more money in the long run. By making more money, we mean you\u2019d lose money at a slower rate than the average lottery player; you\u2019d still lose money.<\/p>\n

Full source code can be found here: https:\/\/github.com\/retowyss\/swisslos-r-griddb\/<\/p>\n

\n
$ git clone https:\/\/github.com\/retowyss\/swisslos-r-griddb.git <\/code><\/pre>\n<\/div>\n
Data Overview and Analysis Tools<\/h2>\n
We analyze approximately seven years of lottery drawings (n = 720, start = 2013-01-12, end = 2019-12-04) from \u201cSwisslos,\u201d the national lottery of Switzerland, using the statistical programming language R, and we use GridDB for our data storage.<\/p>\n
\n
# Required packages RJDBC and tidyverse \n# griddb is the connection object to our GridDB\n\ndrv <- JDBC(\n  driverClass = \"com.toshiba.mwcloud.gs.sql.Driver\",\n  # Point this to your gridstore jar\n  classPath = \"\/jdbc\/bin\/gridstore-jdbc.jar\"\n)\n\n# IP and port depend on your setup\ngriddb <- dbConnect(\n  drv, \n  \"jdbc:gs:\/\/172.20.0.42:20001\/dockerGridDB\/public\", \n  \"admin\", \n  \"admin\"\n)\n\n# vectorized insert function\ndbInsertTable <- function(conn, name, df, append = TRUE) {\n  for (i in seq_len(nrow(df))) {\n    dbWriteTable(conn, name, df[i, ], append = append)\n  }\n}\n\ndbSendUpdate(griddb, paste(\n  \"CREATE TABLE IF NOT EXISTS swisslos_jackpots\", \n  \"(date STRING, jackpot INTEGER);\"\n))\ndbInsertTable(griddb, \"swisslos_jackpots\", read_csv(\"data\/swisslos_jackpots.csv\"))\n\n\ndbSendUpdate(griddb, paste(\n  \"CREATE TABLE IF NOT EXISTS swisslos_payouts\", \n  \"(combination STRING, winners INTEGER, prize FLOAT, date STRING);\"\n))\ndbInsertTable(griddb, \"swisslos_payouts\", read_csv(\"data\/swisslos_payouts.csv\"))\n\n\ndbSendUpdate(griddb, paste(\n  \"CREATE TABLE IF NOT EXISTS swisslos_numbers\", \n  \"(type STRING, number INTEGER, date STRING);\"  \n))\ndbInsertTable(griddb, \"swisslos_numbers\", read_csv(\"data\/swisslos_numbers.csv\"))<\/code><\/pre>\n<\/div>\n
\n
dbListTables(griddb)<\/code><\/pre>\n<\/div>\n
## [1] \"swisslos_jackpots\" \"swisslos_numbers\"  \"swisslos_payouts\"\n<\/code><\/pre>\n
    \n
  • Jackpot sizes (swisslos_jackpots) <\/li>\n<\/ul>\n
      \n
    1. \n    date\n  <\/li>\n
    2. \n    jackpot: maximum payout (CHF) for 6 + 1\n  <\/li>\n<\/ol>\n
        \n
      • Drawn numbers (swisslos_numbers) <\/li>\n<\/ul>\n
          \n
        1. \n    type (normal, lucky, replay)\n  <\/li>\n
        2. \n    number\n  <\/li>\n
        3. \n    date\n  <\/li>\n<\/ol>\n
            \n
          • Payout per category correct (swisslos_payouts) <\/li>\n<\/ul>\n
              \n
            1. \n    combination: normal + lucky (for example, 3 + 1 => three regular correct and lucky number correct)\n  <\/li>\n
            2. \n    winners: number of winning tickets\n  <\/li>\n
            3. \n    prize: payout (CHF) per winner\n  <\/li>\n
            4. \n    date\n  <\/li>\n<\/ol>\n
              \n
              # stringr::str_interp is a handy function to parameterize SQL queries from R\n# just be careful; SQL injections happen.\nshow_date <- function(conn, table, date = \"2013-02-13\") {\n  dbGetQuery(conn, str_interp(\"SELECT * FROM ${table} WHERE date = '${date}';\"))\n} \n\n#only show swisslos_ tables\nmap(keep(dbListTables(griddb), ~ str_detect(., \"swisslos_\")), ~ show_date(griddb, .))<\/code><\/pre>\n<\/div>\n
              ## [[1]]\n##         date jackpot\n## 1 2013-02-13 8600000\n## \n## [[2]]\n##     type number       date\n## 1 normal     13 2013-02-13\n## 2 normal     21 2013-02-13\n## 3 normal     25 2013-02-13\n## 4 normal     26 2013-02-13\n## 5 normal     32 2013-02-13\n## 6 normal     40 2013-02-13\n## 7  lucky      1 2013-02-13\n## 8 replay     13 2013-02-13\n## \n## [[3]]\n##   combination winners   prize       date\n## 1       6 + 1       0    0.00 2013-02-13\n## 2           6       0    0.00 2013-02-13\n## 3       5 + 1       6 7570.15 2013-02-13\n## 4           5      36 1000.00 2013-02-13\n## 5       4 + 1     283  208.90 2013-02-13\n## 6           4    1690   87.35 2013-02-13\n## 7       3 + 1    4681   31.85 2013-02-13\n## 8           3   28264   10.55 2013-02-13\n<\/code><\/pre>\n
              \n
              \n    Swisslos rules and probabilities
              \n  <\/h2>\n
               To play Swisslos, you choose six numbers between 1 and 42 and a single <\/p>\n
                Lucky Number<\/em> between 1 and 6. <\/p>\n
              \n
              regular_numbers <- 42\nregular_draws   <- 6\nlucky_numbers   <- 6\nlucky_draws     <- 1<\/code><\/pre>\n<\/p><\/div>\n
               We can compute the number of possible combinations as follows. Of course, the <\/p>\n
                Lucky Number<\/em> increases the number of combinations by a factor of six. <\/p>\n
              \n
              # Binomial coefficient function\n# Bin(a, b)\nbin <- function(a, b) {\n  map2_dbl(a, b, function(.a, .b) {\n    if (.b == 0 | .a == .b) {\n      1\n    } else {\n      .c <- .a - .b + 1\n      prod(.c:.a) \/ prod(1:.b)\n    }\n  })\n}\n\n# 42 choose 6\nswisslos_regular_combos <- bin(regular_numbers, regular_draws)<\/code><\/pre>\n<\/p><\/div>\n
               There are 5245786 ways to choose six from 42, factoring in the <\/p>\n
                Lucky Number<\/em> there are 31474716 combinations. Similarly, we can calculate the combinations of three, four, and five with or without the lucky number. So, we can calculate the probabilities. <\/p>\n
              \n
              # probability to get n correct\nswisslos_prob <- function(n) {\n  n_match <- bin(regular_draws, n)\n  n_miss <-  bin(regular_numbers - regular_draws, regular_draws - n)\n  n_miss * n_match  \/ swisslos_regular_combos\n} \n\n# We can check correctness with sum(swisslos_prob(0:6)) == 1, which yield true\n\ntibble(n_correct = 0:6) %>% \n  mutate(\n    prob_base  = swisslos_prob(n_correct),\n    prob_lucky = prob_base \/ 6,\n    prob_not_lucky = prob_base - prob_lucky\n  ) %>% \n  knitr::kable(digits = 8)<\/code><\/pre>\n<\/p><\/div>\n